Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I'm currently working on cummulative sum and was stucked in this requirement. This must be working on a line chart which shows line is going upward. Hoping someone might help me into this.
My expected output is in the picture.
I tried,
1. CALCULATE>>FILTER>>ALL>>DATETABLE<=MAX
2. VAR>>RETURN>>SUMX>>FILTER>>ALL>>
But still doesnt give me the output I need. Hoping someone might know the solution.
Thanks for taking your time
Solved! Go to Solution.
Hi @rr_her2555 ,
Please follow the steps below to get the culmulative values:
1. Please DO NOT create any relationship between your fact table and date dimension table
2. Create a measure as below to get it
Total Commulative Sum =
VAR _date =
SELECTEDVALUE ( 'Date'[Date] )
RETURN
CALCULATE (
SUM ( 'Table'[Cost Save] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Go LiveDate] <= _date )
)
In addition, you can refer the following links to get it.
How to Calculate Cumulative Values with DAX - Microsoft Fabric Community
Cumulative sum in Power BI: CALCULATE, FILTER and ALL | by Samuele Conti | Medium
CALCULATE(
SUM(Sales[Revenue]),
FILTER(
ALL(Sales),
Sales[SaleDate] <= MAX(Sales[SaleDate])
)
)
Best Regards
I have seen the above article and it did help me to compute Cummulative using Calculate & All functions. I would like to be able to still use the filters. Using the above example if the table also contains column like brands, country, reporting month, plan/actual etc, how can I use the cummulative when I select a brand or country or any combination there of.
Hi @rr_her2555
can you elaborate on your problem statement? You have a single table with products, Go Live date, and cost_save.
There will be a DIm_date table as well. Your output is for a single month, right?
Hi @Musadev yes, I have a date table. output must be the total cumulative cost saved of products by month.
Hi @rr_her2555 ,
Please follow the steps below to get the culmulative values:
1. Please DO NOT create any relationship between your fact table and date dimension table
2. Create a measure as below to get it
Total Commulative Sum =
VAR _date =
SELECTEDVALUE ( 'Date'[Date] )
RETURN
CALCULATE (
SUM ( 'Table'[Cost Save] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Go LiveDate] <= _date )
)
In addition, you can refer the following links to get it.
How to Calculate Cumulative Values with DAX - Microsoft Fabric Community
Cumulative sum in Power BI: CALCULATE, FILTER and ALL | by Samuele Conti | Medium
CALCULATE(
SUM(Sales[Revenue]),
FILTER(
ALL(Sales),
Sales[SaleDate] <= MAX(Sales[SaleDate])
)
)
Best Regards
I have seen the above article and it did help me to compute Cummulative using Calculate & All functions. I would like to be able to still use the filters. Using the above example if the table also contains column like brands, country, reporting month, plan/actual etc, how can I use the cummulative when I select a brand or country or any combination there of.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |