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 everyone,
I have a table that looks something like this;
and I want to be able to create a running total of the amount column and have two slicers controlling the output.
The first slicer is country which shows only the states in the country when triggered
the second slicer (where the magic happens) is for state, and gives the sum for the respective state
My current formula (below) only returns the sum on a country level but not a state level
Amount Targeted =
VAR endOfPeriod = MAX ( 'Calendar'[Date] )
VAR startOfPeriod = MIN( 'Calendar'[Date] )
RETURN
CALCULATE (
SUM(Table[amount]),
FILTER (
ALL(Table),
( Table[Date] <= endOfPeriod
)
))
Thank you in advance for your help 🙂
Solved! Go to Solution.
Hi @sloane ,
Create a measure as below:
Measure = IF(ISFILTERED('Table'[State]),
CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[State]=SELECTEDVALUE('Table'[State]))),
CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date]))))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@sloane , Try like
Amount Targeted =
CALCULATE (
SUM(Table[amount]),
FILTER (
ALLSELECTED(Table),
( Table[Date] <= max(Table[Date])
)
))
Thanks but this still doesnt work when I filter the state slicer. It only works when I filter the country slicer
Hi @sloane ,
Create a measure as below:
Measure = IF(ISFILTERED('Table'[State]),
CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[State]=SELECTEDVALUE('Table'[State]))),
CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date]))))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |