Doubt with weighted average, sumx not doing what I want  [Solved]

Anything related to PowerPivot and DAX Formuale
Alexanderholly
Posts: 4
Joined: Thu Dec 27, 2018 7:34 am

Doubt with weighted average, sumx not doing what I want

Postby Alexanderholly » Thu Dec 27, 2018 8:06 am

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 Jan-1st
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
Attachments
ExamplePowerpivot.xlsx
(202.42 KiB) Downloaded 32 times

MattAllington
Posts: 1015
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Doubt with weighted average, sumx not doing what I want

Postby MattAllington » Fri Dec 28, 2018 5:05 pm

Attachments
ExamplePowerpivot.xlsx
(186.7 KiB) Downloaded 32 times
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Alexanderholly
Posts: 4
Joined: Thu Dec 27, 2018 7:34 am

Re: Doubt with weighted average, sumx not doing what I want

Postby Alexanderholly » Sat Dec 29, 2018 10:36 am

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?

2-Is 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 30 times

MattAllington
Posts: 1015
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Doubt with weighted average, sumx not doing what I want

Postby MattAllington » Sun Dec 30, 2018 8:53 am

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.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Alexanderholly
Posts: 4
Joined: Thu Dec 27, 2018 7:34 am

Re: Doubt with weighted average, sumx not doing what I want  [Solved]

Postby Alexanderholly » Mon Dec 31, 2018 3:18 pm

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 Date-Location
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 Date-Loation 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 31 times

MattAllington
Posts: 1015
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Doubt with weighted average, sumx not doing what I want

Postby MattAllington » Tue Jan 01, 2019 7:52 am

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

ExamplePowerpivot new approach.xlsx
(483.12 KiB) Downloaded 30 times


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

Alexanderholly
Posts: 4
Joined: Thu Dec 27, 2018 7:34 am

Re: Doubt with weighted average, sumx not doing what I want

Postby Alexanderholly » Wed Jan 02, 2019 2:51 am

Hello Matt, basically you solved the problem I had, I just got a couple of last questions

1-Where can I learn more about proper modelling?
2-The pivot table mentions that some relationships may be needed, but looking at the relationships and :geek: the results, everything looks fine to me.
3-The 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.
4-What is the difference between
Units per day:=DIVIDE(sum(Deliveries[Units]),[SelectedDays])
and
Units per day:=sum(Deliveries[Units])/[SelectedDays]) ?
Thank you for all! :geek:

MattAllington
Posts: 1015
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Doubt with weighted average, sumx not doing what I want

Postby MattAllington » Wed Jan 02, 2019 8:22 am

Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 6 guests

cron