Forum Discussion
Cumulative total based on column type
Hello,
I am trying to create line and clustered column chart
The line line graph will be the cumulative total by Type (budget, forecast) .
How to do I calcuate two cumulative total , one for budget, another for Forecast.
I have table design like this :
Team, Type, Months, Value, MonthSort
S Budget 1 100 1
S Forecast 1 500 1
Thank you for your help
Hi Anonymous
Create Measures
Measure = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Type] = MAX ( 'Table'[Type] ) && 'Table'[date] <= MAX ( 'Table'[date] ) ) ) or Measure 2 = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Type] = MAX ( 'Table'[Type] ) && 'Table'[MonthSort] <= MAX ( 'Table'[MonthSort] ) ) )Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous
Create Measures
Measure = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Type] = MAX ( 'Table'[Type] ) && 'Table'[date] <= MAX ( 'Table'[date] ) ) ) or Measure 2 = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Type] = MAX ( 'Table'[Type] ) && 'Table'[MonthSort] <= MAX ( 'Table'[MonthSort] ) ) )Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.You would create two measures, one that uses a FILTER for budget and one for forecast. Hard to be more specific. If you need it to be cumulative, you would base your cumulation on Months or Month Sort I would guess. Not sure, not much data provided to provide a sense of the overall source data.
In case you have date and it joined with date dimension, this is the way to get cumulative
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date] <=maxx(date,date[date]))) Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date] <=maxx(date,endofmonth(dateadd(date[date]),-1,month))))You can also get like
Cumm Sales = CALCULATE(SUM(Table[Sales Amount]),filter(all(table),table[Month sort] <=maxx(table,Table[Month sort])))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin
