Forum Discussion
Help with doing a cumulative total
Hi, psmithAPS
Thank you for your prompt response.
Firstly,I'm glad to hear that you're interested in visual calculations. However, I should explain that visual calculations are only applicable to report views.
In table view, visible DAX calculations are either calculated columns or calculated tables:
If you want DAX calculations to be visible in table view, you can try the second solution I mentioned earlier, but with a slight modification:
1.Firstly, create a calculation table and aggregate the values:
Table = SUMMARIZE( 'virtual_data', 'virtual_data'[Date].[Year], 'virtual_data'[Date].[Month],'virtual_data'[Date].[MonthNo], "cx", COUNT('virtual_data'[xx]), "cl", COUNT('virtual_data'[ll]) )2.Secondly, create the following calculated column:
Column1= CALCULATE ( SUM ( 'Table'[cl] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[MonthNo] <= EARLIER( 'Table'[MonthNo] ) && 'Table'[Year] =EARLIER( ( 'Table'[Year] ) ) ))Column 2 = CALCULATE ( SUM ( 'Table'[cx] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[MonthNo] <= EARLIER( 'Table'[MonthNo] ) && 'Table'[Year] =EARLIER( ( 'Table'[Year] ) ) ))If you prefer to use visual calculations, you can try the first solution I mentioned earlier.
3.Here's my final result, which I hope meets your requirements.
4.You may need to note that if the total in the matrix is not calculated in the way you desire, you can use the following measure:
MEASURE = IF ( ISINSCOPE ( 'Table'[Year] ), MAX ( 'Table'[Column1] ), SUM ( [cl] ) )Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi psmithAPS ,
Supposing you have a date column, try to
1.add a calculated column like:
yy/mm = FORMAT(data[date], "yy/mm")
2.plot the visual with yy/mm column and a measure like:
cumulative total of actual start =
CALCULATR(
COUNT(data[actual start]),
data[yy/mm]<=MAX(data[yy/mm])
)
I am unsure whether I need or how to go about inputting a date column, every piece of data is tied to an ID value, and there are several dates tied to each ID, the schedule start and finish date, the actual start date and finish date, the baseline start and finish date, and the planned start and finish date. The schedule start date is what I use to get the date range of each count it is the X axis of the chart purely because it is the only date that will not skew the date data of the other types. The actual start date and the baseline start dates of each activity create the counts, just like a pivot table counting the number of activities in the schedule that fall within a set number of dates. I may not be understanding the role of a date column in Power BI.
Hi, psmithAPS
Thank you for your prompt response.
You can try the following steps in Power Query to generate a new date column:
1.Firstly, here is my sample data:
2.Secondly, I added a custom column in Power Query:
Date.FromText(Text.From([Year]) & "-" & Text.PadStart(Text.From([Month]), 2, "0") & "-01")3.Next, I modified its data type:
4.Below is the final result:
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The problem with this solution appears to be that the dates I pull from already are in mm/dd/yyyy format prior to starting the chart, there is no text date to combine until I utilize the start date as the X-axis data. Is there a visual calculation that allows for a cumulative sum within the table view of the data.
Hi, psmithAPS
Thank you for your prompt response.
Firstly,I'm glad to hear that you're interested in visual calculations. However, I should explain that visual calculations are only applicable to report views.
In table view, visible DAX calculations are either calculated columns or calculated tables:
If you want DAX calculations to be visible in table view, you can try the second solution I mentioned earlier, but with a slight modification:
1.Firstly, create a calculation table and aggregate the values:
Table = SUMMARIZE( 'virtual_data', 'virtual_data'[Date].[Year], 'virtual_data'[Date].[Month],'virtual_data'[Date].[MonthNo], "cx", COUNT('virtual_data'[xx]), "cl", COUNT('virtual_data'[ll]) )2.Secondly, create the following calculated column:
Column1= CALCULATE ( SUM ( 'Table'[cl] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[MonthNo] <= EARLIER( 'Table'[MonthNo] ) && 'Table'[Year] =EARLIER( ( 'Table'[Year] ) ) ))Column 2 = CALCULATE ( SUM ( 'Table'[cx] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[MonthNo] <= EARLIER( 'Table'[MonthNo] ) && 'Table'[Year] =EARLIER( ( 'Table'[Year] ) ) ))If you prefer to use visual calculations, you can try the first solution I mentioned earlier.
3.Here's my final result, which I hope meets your requirements.
4.You may need to note that if the total in the matrix is not calculated in the way you desire, you can use the following measure:
MEASURE = IF ( ISINSCOPE ( 'Table'[Year] ), MAX ( 'Table'[Column1] ), SUM ( [cl] ) )Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.