Good afternoon all,
I hope all are doing well, I have the following model with two tables:
Dates: Date Dimension table (jan 1st to feb 28th in this example)
Fact table: it contains the following columns
Schedule month: it referrs to the month on the row context, but its expressed by day. If I want to referr to Jan, it is expressed as Jan1st
Delivery Location: no need to pay attention to this
Scheduled delivery per day: this gives the average items to be delivered per day during that month
These two tables are related by Date[Date] as primary key and FactTable[Schedule Month] as Foregin Key.
What I want to do is have a filter for month and days and do the calculations based on the filter context as follows:
Example:
Filtered days: Jan 24th to Feb 9th, gives 15 days in total
Days per month: 8 days in January and 9 in February
If January has on average a delivery schedule of 4 and February of 7, the subtotal for A should display= (8*4+9*7)/17=5.58 items on average for the selected period of time. The next step would be to sum the subtotal of all destinations
The DAX I am using is:
Daysfiltered: Returns the number of days that are being filtered by month
daysfiltered:=if(ISFILTERED(D_DateTable[Dates (day)]),COUNT(D_DateTable[Date]),0)
Daystotal: Returns the total of days that are being filtered
daystotal:=CALCULATE(COUNT(D_DateTable[Dates (day)]),ALLEXCEPT(D_DateTable,D_DateTable[Dates (day)]))
Weighted schedule: It should return the sumproduct of the Scheduled delivery times the days of the current month and divide by the total of days that are being filtered.
two issues:
I am not getting the result I expect of lets say 5.58.
If I do not select jan 1st and feb 1st obviously I dont get any result because of the model itself, since it cannot retrieve any value from the related table.
How can I solve these two issues?
I attach the model so you can have a better look on how is everything built. Hopefully someone can help me out on this one
thank you for the support
Doubt with weighted average, sumx not doing what I want [Solved]

 Posts: 4
 Joined: Thu Dec 27, 2018 7:34 am
Doubt with weighted average, sumx not doing what I want
 Attachments

 ExamplePowerpivot.xlsx
 (202.42 KiB) Downloaded 54 times

 Posts: 1050
 Joined: Sun May 04, 2014 4:01 pm
 Location: Sydney, Australia
Re: Doubt with weighted average, sumx not doing what I want
I suggest a month level calendar table with 1 row per month and the number of days in the month. Then write 3 measures so solve the problem. Break the problem into bits and solve each bit one at a time
Have a look at this
Have a look at this
{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}
=
SUMX (
CalendarNew,
CALCULATE ( SUM ( FactTable[Scheduled delivery per day] ) )
)
{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}
=
CALCULATE (
SUMX ( CalendarNew, CalendarNew[Days in Month] ),
FILTER ( FactTable, [Daily Average Del] <> 0 )
)
{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}
=
SUMX ( CalendarNew, [Daily Average Del] * [Total Days] )
/ SUMX ( CalendarNew, [Total Days] )
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training

 Posts: 4
 Joined: Thu Dec 27, 2018 7:34 am
Re: Doubt with weighted average, sumx not doing what I want
Hello Matt, I really appreciate that you took your time to look into this, I have a couple of questions:
1
=
SUMX (
CalendarNew,
CALCULATE ( SUM ( FactTable[Scheduled delivery per day] ) )
)
Why the need to put a sum inside a sum x?
2Is it possible the user can filter the dates they want to use in a slicer? In the example you provided, the model gives the total days per month. What I want my application to do, is to compare actual data made on certain dates, and compare it to what is scheduled. I added a new fact2 table which is related to fact table 1. Also I put an example of what I want to show on the pivot table worksheet. Hopefully you can give it a look and let me know your thoughts,
Thanks for the support
1
=
SUMX (
CalendarNew,
CALCULATE ( SUM ( FactTable[Scheduled delivery per day] ) )
)
Why the need to put a sum inside a sum x?
2Is it possible the user can filter the dates they want to use in a slicer? In the example you provided, the model gives the total days per month. What I want my application to do, is to compare actual data made on certain dates, and compare it to what is scheduled. I added a new fact2 table which is related to fact table 1. Also I put an example of what I want to show on the pivot table worksheet. Hopefully you can give it a look and let me know your thoughts,
Thanks for the support
 Attachments

 ExamplePowerpivot (1).xlsx
 (258.46 KiB) Downloaded 50 times

 Posts: 1050
 Joined: Sun May 04, 2014 4:01 pm
 Location: Sydney, Australia
Re: Doubt with weighted average, sumx not doing what I want
1. Good question  it's complicated. The syntax is SUMX(table,expression)
It works one row at a time in a table (each row in the calendar table in this case) and for each row it works out the result of the expression. If there are 10 rows in the table then the expression is evaluated 10 times. After they are all evaluated (10 of them), THEN the values are added up by SUMX. So the expression could be anything, but in this case it is SUM. It needs to be SUM because the column of data being added up is not in the calendar table (in this case). If the column WAS in the calendar table, then you wouldn't need CALCULATE(SUM())
2. The sample data you originally loaded suggested that your fact data was at the month level and there is only a single fact table. That is why I proposed the solution I did. Now it is clear that this is not the case and you actually need day level data and for some reason you have 2 fact tables. I don't understand this data structure or why you have it set up this way. My suggested month calendar table is not the best approach here.
Can you please explain the full extent to the problem you are trying to solve. What is the source of your data, why you have 2 fact tables etc. You should then load some realistic data and a day level calendar table and post a sample.
It works one row at a time in a table (each row in the calendar table in this case) and for each row it works out the result of the expression. If there are 10 rows in the table then the expression is evaluated 10 times. After they are all evaluated (10 of them), THEN the values are added up by SUMX. So the expression could be anything, but in this case it is SUM. It needs to be SUM because the column of data being added up is not in the calendar table (in this case). If the column WAS in the calendar table, then you wouldn't need CALCULATE(SUM())
2. The sample data you originally loaded suggested that your fact data was at the month level and there is only a single fact table. That is why I proposed the solution I did. Now it is clear that this is not the case and you actually need day level data and for some reason you have 2 fact tables. I don't understand this data structure or why you have it set up this way. My suggested month calendar table is not the best approach here.
Can you please explain the full extent to the problem you are trying to solve. What is the source of your data, why you have 2 fact tables etc. You should then load some realistic data and a day level calendar table and post a sample.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training

 Posts: 4
 Joined: Thu Dec 27, 2018 7:34 am
Re: Doubt with weighted average, sumx not doing what I want [Solved]
Good evening Matt,
thank you for trying to help me, I agree with you, I should of have loaded the complete model first in order to best describe what I need. In the document attached you will find the following tables:
Dates: The date dimention with the day level.
CalendarNew: The month dimention with month level of aggregation.
T1: Contains the highest level of granularity of the model, it contains how much units should be delivered on average per day on a certain month and per location. Primary Key DateLocation
T2: Contains the next level of aggregation, in this table, there are different purchase orders for one same location and one same month, these usually try to meet the total units needed on T1 but it is not always the case. The logic behind this, is that you have a requirement on T1, but T2 describes the different suppliers the commercial team get in order to try to meet the needs per destination per month in T1. Sometimes there is not enough product to meet the needs on T1. Foreing Key DateLoation and Primary Key Purchase Order
T3: Contains the actual units delivered each day per purchase order. The foreig Key is Purchase Order
The headers of the tables highlighted in orange have comments in order to understand better the tables. On the pivot table ribbon, there is a handmade table which shows the desired results of the pivot table above it. I am not an expert with modelling, based on what I know, that is the best model that came to my mind. If you have a better approach I would be happy to apply it. If you can find a way to reproduce the pivot I'm looking for, that would be great.
thank you for the support and the time invested
thank you for trying to help me, I agree with you, I should of have loaded the complete model first in order to best describe what I need. In the document attached you will find the following tables:
Dates: The date dimention with the day level.
CalendarNew: The month dimention with month level of aggregation.
T1: Contains the highest level of granularity of the model, it contains how much units should be delivered on average per day on a certain month and per location. Primary Key DateLocation
T2: Contains the next level of aggregation, in this table, there are different purchase orders for one same location and one same month, these usually try to meet the total units needed on T1 but it is not always the case. The logic behind this, is that you have a requirement on T1, but T2 describes the different suppliers the commercial team get in order to try to meet the needs per destination per month in T1. Sometimes there is not enough product to meet the needs on T1. Foreing Key DateLoation and Primary Key Purchase Order
T3: Contains the actual units delivered each day per purchase order. The foreig Key is Purchase Order
The headers of the tables highlighted in orange have comments in order to understand better the tables. On the pivot table ribbon, there is a handmade table which shows the desired results of the pivot table above it. I am not an expert with modelling, based on what I know, that is the best model that came to my mind. If you have a better approach I would be happy to apply it. If you can find a way to reproduce the pivot I'm looking for, that would be great.
thank you for the support and the time invested
 Attachments

 ExamplePowerpivot (1).xlsx
 (425.96 KiB) Downloaded 49 times

 Posts: 1050
 Joined: Sun May 04, 2014 4:01 pm
 Location: Sydney, Australia
Re: Doubt with weighted average, sumx not doing what I want
Well, I don't really understand to be honest, but have a look at what I have done. This is actually a data modelling issue, not a dax issue and hence why the entire data model is needed for context and to make sure the solution is designed correctly
Also, I highly recommend that you don't call your tables T1, T2 etc. Give them a descriptive noun to help clarify their purpose and content.
Also, I highly recommend that you don't call your tables T1, T2 etc. Give them a descriptive noun to help clarify their purpose and content.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training

 Posts: 4
 Joined: Thu Dec 27, 2018 7:34 am
Re: Doubt with weighted average, sumx not doing what I want
Hello Matt, basically you solved the problem I had, I just got a couple of last questions
1Where can I learn more about proper modelling?
2The pivot table mentions that some relationships may be needed, but looking at the relationships and the results, everything looks fine to me.
3The only calculation im missing is the totals for scheduled deliveries per day, manual calculation made on the below table, wondering if you can help me with that one.
4What is the difference between
Units per day:=DIVIDE(sum(Deliveries[Units]),[SelectedDays])
and
Units per day:=sum(Deliveries[Units])/[SelectedDays]) ?
Thank you for all!
1Where can I learn more about proper modelling?
2The pivot table mentions that some relationships may be needed, but looking at the relationships and the results, everything looks fine to me.
3The only calculation im missing is the totals for scheduled deliveries per day, manual calculation made on the below table, wondering if you can help me with that one.
4What is the difference between
Units per day:=DIVIDE(sum(Deliveries[Units]),[SelectedDays])
and
Units per day:=sum(Deliveries[Units])/[SelectedDays]) ?
Thank you for all!

 Posts: 1050
 Joined: Sun May 04, 2014 4:01 pm
 Location: Sydney, Australia
Re: Doubt with weighted average, sumx not doing what I want
1Where can I learn more about proper modelling?
Tricky. I suggest reading this blog article I wrote data modelling in Power BI. The best book I know of is this one, but I think it is probably too detailed for what you need. https://www.sqlbi.com/blog/marco/2017/0 ... votusers/
If you stick with "trying" to get a star schema, you will solve most problems
2The pivot table mentions that some relationships may be needed, but looking at the relationships and the results, everything looks fine to me.
Yes, it is a pain, and you can't turn it off. It is a false warning.
3The only calculation im missing is the totals for scheduled deliveries per day, manual calculation made on the below table, wondering if you can help me with that one.
4What is the difference between
Units per day:=DIVIDE(sum(Deliveries[Units]),[SelectedDays])
and
Units per day:=sum(Deliveries[Units])/[SelectedDays]) ?
The DIVIDE function will handle a divide by zero error automatically.
Tricky. I suggest reading this blog article I wrote data modelling in Power BI. The best book I know of is this one, but I think it is probably too detailed for what you need. https://www.sqlbi.com/blog/marco/2017/0 ... votusers/
If you stick with "trying" to get a star schema, you will solve most problems
2The pivot table mentions that some relationships may be needed, but looking at the relationships and the results, everything looks fine to me.
Yes, it is a pain, and you can't turn it off. It is a false warning.
3The only calculation im missing is the totals for scheduled deliveries per day, manual calculation made on the below table, wondering if you can help me with that one.
{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}
=SUMX (
YYMM,
CALCULATE (
SUM ( Targets[Scheduled delivery per day (units/day)]) * COUNTROWS ( 'Calendar' )
)
)
/ COUNTROWS ( calendar )
4What is the difference between
Units per day:=DIVIDE(sum(Deliveries[Units]),[SelectedDays])
and
Units per day:=sum(Deliveries[Units])/[SelectedDays]) ?
The DIVIDE function will handle a divide by zero error automatically.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training
Who is online
Users browsing this forum: No registered users and 1 guest