Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am looking at creating a total sum per month as below in the Running Total.
Year | Region | Cat | Category | MONTH | Savings | Running Total |
2018 | A | XX | Services | January | 50 | 50 |
2018 | A | XX | Services | February | 65 | 115 |
2018 | A | XX | Services | March | 45 | 160 |
2018 | B | YY | Retail | January | 21 | 21 |
2018 | B | YY | Retail | February | 45 | 66 |
2018 | B | YY | Retail | March | 23 | 89 |
2018 | C | ZZ | Equipment | January | 12 | 12 |
2018 | C | ZZ | Equipment | February | 55 | 67 |
2018 | C | ZZ | Equipment | March | 66 | 133 |
I can see how to do this in DAX but I am little confused as I am working with Text and not dates (I do also have a column called Month Number (1-12) per month which i might be able to use?
Is possible via DAX?
Thanks
Solved! Go to Solution.
@Anonymous
Try this as a MEASURE
Running_Total = CALCULATE ( SUM ( Targets[Savings] ), FILTER ( ALLEXCEPT ( Targets, Targets[Category] ), Targets[Date] <= SELECTEDVALUE ( Targets[Date] ) ) )
or this as a CALCULATED COLUMN
Running_Total = CALCULATE ( SUM ( Targets[Savings] ), FILTER ( ALLEXCEPT ( Targets, Targets[Category] ), Targets[Date] <= EARLIER ( Targets[Date] ) ) )
@Anonymous
I think that whatever Column you use as a slicer would need to be part of ALLEXCEPT function. For example if you use region as a SLICER
Then Measure would be
Running_Total = CALCULATE ( SUM ( Targets[Savings] ), FILTER ( ALLEXCEPT ( Targets, Targets[Category] ,Targets[Region]), Targets[Date] <= SELECTEDVALUE ( Targets[Date] ) ) )
I'd try to create a dummy date field from the information you have - calculate a column through DATE ([Year], [whateveryourmonthnumbercolumniscalled],1) which should create a field with the first day of the month in question
Great i have made that but when i put the Dax in to the new mesure it does not roll up per month;
My dax i have used is below;
Saving Roll =
CALCULATE (
SUM ( Targets[Savings] ),
FILTER (
ALL ( Targets[Date] ),
Targets[Date] <= MAX ( Targets[Date] )
)
)
Wonder what could be going wrong?
@Anonymous
Try this as a MEASURE
Running_Total = CALCULATE ( SUM ( Targets[Savings] ), FILTER ( ALLEXCEPT ( Targets, Targets[Category] ), Targets[Date] <= SELECTEDVALUE ( Targets[Date] ) ) )
or this as a CALCULATED COLUMN
Running_Total = CALCULATE ( SUM ( Targets[Savings] ), FILTER ( ALLEXCEPT ( Targets, Targets[Category] ), Targets[Date] <= EARLIER ( Targets[Date] ) ) )
Arr thought it was 100% but this woks if the slicer is Catrgory but not if it is any of the others slicers 😞
@Anonymous
When I use your data and use Region as slicer...It seemingly works well.
What slicer did you use?
Arr yes but in my whole dataset it is a little different, I have an update example of what could happen.
As you can see from below Region Cat and Category can be different the was luck that Region Cat and Category lined up 😞
That is why the sum always looked correct.
Year | Region | Cat | Category | MONTH | Savings | Running Total |
2018 | A | XX | Services | January | 50 | 50 |
2018 | A | XX | Services | February | 65 | 115 |
2018 | B | ZZ | Services | March | 45 | 160 |
2018 | A | YY | Retail | January | 21 | 21 |
2019 | B | XX | Retail | February | 45 | 66 |
2018 | B | YY | Retail | March | 23 | 89 |
2018 | B | ZZ | Equipment | January | 12 | 12 |
2018 | C | ZZ | Equipment | February | 55 | 67 |
2019 | C | YY | Equipment | March | 66 | 133 |
Adding to that I am using lots of slicers just the normal oob ones but Cat,Month,category etc..
@Anonymous
I think that whatever Column you use as a slicer would need to be part of ALLEXCEPT function. For example if you use region as a SLICER
Then Measure would be
Running_Total = CALCULATE ( SUM ( Targets[Savings] ), FILTER ( ALLEXCEPT ( Targets, Targets[Category] ,Targets[Region]), Targets[Date] <= SELECTEDVALUE ( Targets[Date] ) ) )
Great just updated my report and that dose seem to be the case thanks for all of your help
Thanks that works, starting the under stand Dax better now!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |