Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm truly stumped with this one.
I am trying to show the sales for different products, and brands, for different months. I have created measures, as I also want to be able to show the sales amounts against a certain reference or baseline month.
Now, one condition is that I need to be able to slice the pivot table by project status.
The measures work, except the Baseline Measures always show the total amounts, regardless of what the "project status" slicer is filtered on. So it will always show the same total amount, regardless of whether only cancelled projects are selected, or only active projects are selected (always only showing the amount as if both active and cancelled were selected), which I don't want.
Here are the picutres to show what is happening...
Here is the DAX:
Total Sales = CALCULATE(SUM(Main_Data[Sales]))
Total Sales (Baseline) =
IF (
ISBLANK ( [Total Sales] ),
BLANK (),
CALCULATE (
SUM ( Main_Data[Sales] ),
ALL ( Snapshot ),
FILTER ( ALL ( Snapshot[Snapshot] ), Snapshot[Snapshot] = "Baseline" ),
FILTER ( ALL ( Main_Data[Status] ), Main_Data[Status] = "Baseline" )
)
)
Total Sales (Baseline)2 =
IF (
ISFILTERED ( Main_Data[Project] ),
[Total Sales (Baseline)],
SUMX (
VALUES(Main_Data[Role]),
IF (
ISBLANK ( [Total Sales] ),
BLANK (),
CALCULATE (
SUM ( Main_Data[Sales] ),
ALL ( Snapshot ),
FILTER ( ALL ( Snapshot[Snapshot] ), Snapshot[Snapshot] = "Baseline" ),
FILTER ( ALL ( Main_Data[Status] ), Main_Data[Status] = "Baseline" )
)
)
)
)
Data looks like:
DAX Experts, Please help!
Here is the pbix file!
Solved! Go to Solution.
I tried creating a separate dimension table for Status, but now get completely bogus behaviour!
See attached:
Perfect!
Please mark the most appropriate post as an answer, this will help others! And you might also consider to give that post a kudo, if it saved you headache and time 😉
Cheers,
Tom
Hey,
I'm wondering if this will provide what you are looking for ...
My data model (I do not conisder the table DAX measures)
My measure "Total Sales (Baseline)" looks like this
Total Sales (Baseline) = SUMX( VALUES(Main_Data[Project]) ,IF(NOT(ISBLANK([Total Sales])) ,CALCULATE ( SUM ( Main_Data[Sales] ) ,ALL(Snapshot[Snapshot]) ,ALL('Status'[Status]) ,Snapshot[Snapshot] = "Baseline" ,'Status'[Status] = "Baseline" ) ,BLANK() ) )
Using this measure the matrix looks like this:
Please be aware that the slicers are using the dedicated tables (dimension tables), and that I'm also using the status column from Status table in the matrix visual.
Hopefully this is what you are looking for.
Regards,
Tom
Thanks Tom,
This could be it!
Could you send back the pbix file. I've tried to replicate the results, but instead, now the total rows show, but the rows beneath come up as blank...
I think I solved it. I wasn't using the Status field from the Status slicer in the Matrix Table!
It seems to work now.
Thank you!
Perfect!
Please mark the most appropriate post as an answer, this will help others! And you might also consider to give that post a kudo, if it saved you headache and time 😉
Cheers,
Tom
Hi Tom,
Here is my updated pbix file.
Can you see where I am going wrong?
KR,
Sachin
Hey,
please consider to provide a PBIX file that contains the sample data, upload the PBIX file to onedrive or dropbox and share the link.
By the way, it's never a good idea to use ALL in combination with the fact table where the numerical column resides that is used in the expression
'Main_Data'[Sales]
You should consider to create a separate dimension for the Status Column.
Regards,
Tom
Regards,
Tom
As requested, I've uploaded the PBIX file
I tried creating a separate dimension table for Status, but now get completely bogus behaviour!
See attached:
Hey,
thanks for sharing the PBIX file, can you please upload the corresponding "Test Data.xlsx" also.
Regards,
Tom
Hi Tom,
Here is the raw data file...
I've spent hours trying to solve this, but am just getting stumped...it must be a common problem for so many users!
Tried ALLSELECTED to ignore internal filters and keep external filters, but nothing seems to work... : (
Hopefully, the DAX Experts around can help me crack it!
I've set bi-directional filtering on between the Status Table and Main Sales Table which seems to help, but still trouble with the totals not adding up the rows beneath, when the status slicer is used
Sachin
Maybe I need a more sophisticated measure for the total rows...
Reading this... https://www.daxpatterns.com/parent-child-hierarchies/
But still scratching my head... : (
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |