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.
Hello,
I want to create a barchart that will show the cululative total sales over a period of selected months by the user. In the X-axis will be the month(s) and in the Y-axis the sales measure.
My struggling point is that in Y-axis I use so far a measure that includes a SELECTEDVALUE function, since I have a filter with 3 different Sales metrics:
Metric =
VAR MySelection =
SELECTEDVALUE ( 'Measures'[Selection], "Sales" )
RETURN
SWITCH (
TRUE (),
MySelection = "Value", [Sales Value],
MySelection = "Items", [Sales Items],
MySelection = "Units", [Sales Units],
blank()
)
Any idea how can I convert my DAX code above, in order to get cumulative Sales?
Thanks!
Solved! Go to Solution.
Firstly, rather than your current solution you should look into using a fields parameter. they are specifically designed to allow the user to choose which metric to show.
To solve the problem of being able to toggle between accumulated or not you could use a calculation group with 2 items. The first item would just call SELECTEDMEASURE(), so returning the non-cumulative values. The second calculation item could be
Cumulative in period =
VAR StartDate =
CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR EndDate =
MAX ( 'Date'[Date] )
VAR Result =
CALCULATE (
SELECTEDMEASURE (),
DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
)
RETURN
Result
Firstly, rather than your current solution you should look into using a fields parameter. they are specifically designed to allow the user to choose which metric to show.
To solve the problem of being able to toggle between accumulated or not you could use a calculation group with 2 items. The first item would just call SELECTEDMEASURE(), so returning the non-cumulative values. The second calculation item could be
Cumulative in period =
VAR StartDate =
CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR EndDate =
MAX ( 'Date'[Date] )
VAR Result =
CALCULATE (
SELECTEDMEASURE (),
DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
)
RETURN
Result
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 |
---|---|
44 | |
28 | |
21 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |