Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am really stuck and need some guidance.
I have 5 years worth of sales data which I have converted to a % of total sales over the selected period. I have also built a measure that allows me to cumulate these percentages to 100% over the selected period of time.
What I cannot figure out is the following: I want to be able to look at the cumulative % development for each year (from Jan-Dec) on the same line chart - using DAX.. I can look at one year at a time, but if I select all the years they stack to cumulatively get me to 100%. I need to see them discretly built to 100%.
How I get the percentage of volume to total volume per month:
Above is wrong (last column) because if I just select 2017 I get this result, which is correct.
I am new to Power BI and am learning, so any help and explanation as to how to approach this would be greatly appreciated.
Solved! Go to Solution.
In this example you should use simple time intelligence functionalities.
I would calculate the measure using the DATESYTD and then it should relflect the correct figures.
Assuming that you have a calendar table, you can use this measure:
Volume YTD% =
DIVIDE (
CALCULATE (
SUM ( Greg_SCOPS_Excellence[Base Quantity (SKU)] ),
DATESYTD ( 'Calendar'[Date] )
),
CALCULATE (
SUM ( Greg_SCOPS_Excellence[Base Quantity (SKU)] ),
ALLEXCEPT ( 'Calendar', 'Calendar'[Year] )
)
)
In this example you should use simple time intelligence functionalities.
I would calculate the measure using the DATESYTD and then it should relflect the correct figures.
Assuming that you have a calendar table, you can use this measure:
Volume YTD% =
DIVIDE (
CALCULATE (
SUM ( Greg_SCOPS_Excellence[Base Quantity (SKU)] ),
DATESYTD ( 'Calendar'[Date] )
),
CALCULATE (
SUM ( Greg_SCOPS_Excellence[Base Quantity (SKU)] ),
ALLEXCEPT ( 'Calendar', 'Calendar'[Year] )
)
)
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |