Forum Discussion
Add YTD Column in stacked column chart
Hi, I'm currently looking to add an additional column for YTD totals to a chart that currently has Quarters on the x axis. The idea would be that the YTD column would sit after Q4 and would simply update as we go through the year with the full total count and % Performance. The quarter is coming from my date table with the column values all being generated from measures in my main data table.
Late = CALCULATE(DISTINCTCOUNT(Data[STT]), FILTER(Data,Data[Net Performance]="MISS"))
On Time = CALCULATE(DISTINCTCOUNT(Data[STT]), FILTER(Data,Data[Net Performance]="OK"))
% Performance = DIVIDE(Data[Data Nett Shipments OK],Data[Data Total Shipments],0)
Quarter = CONCATENATE("Q",ROUNDUP(MONTH('Date'[Date])/3,0))Many thanks
Hi Gazsim44 ,
Please try this:
1. Enter data to create a Columns table like below and then sort "Columns" column by "Order" column.
Note: no relationship between this new table and your data table.
Columns Order Q1 1 Q2 2 Q3 3 Q4 4 YTD 5 2. Create measures like so:
Late Measure = VAR Columns_ = SELECTEDVALUE ( 'Columns'[Columns] ) RETURN SWITCH ( Columns_, "YTD", CALCULATE ( [YTD], Data[Net Performance] = "MISS" ), CALCULATE ( [Late], Data[Quarter] = Columns_ ) )On Time Measure = VAR Columns_ = SELECTEDVALUE ( 'Columns'[Columns] ) RETURN SWITCH ( Columns_, "YTD", CALCULATE ( [YTD], Data[Net Performance] = "OK" ), CALCULATE ( [On Time], Data[Quarter] = Columns_ ) )3. Put 'Columns'[Columns] into "Axis" field and [Late Measure], [On Time Measure] into "Values" field to create a visual.
You can this post: Possible Analytics function: dynamic Total column in chart with USA States?
Not exactly the same, but the logic is the same.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Gazsim44 , With help from date table and Time intelligence you can try measure like one in example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Pamitchandak Thanks for your reply although not sure this is what I need?
I am looking to add an additional column for YTD and not just an additional measure to add to the existing columns.
So in effect your columns would be Q1, Q2, Q3, Q4 and YTD,
Thanks
Hi Gazsim44 ,
Please try this:
1. Enter data to create a Columns table like below and then sort "Columns" column by "Order" column.
Note: no relationship between this new table and your data table.
Columns Order Q1 1 Q2 2 Q3 3 Q4 4 YTD 5 2. Create measures like so:
Late Measure = VAR Columns_ = SELECTEDVALUE ( 'Columns'[Columns] ) RETURN SWITCH ( Columns_, "YTD", CALCULATE ( [YTD], Data[Net Performance] = "MISS" ), CALCULATE ( [Late], Data[Quarter] = Columns_ ) )On Time Measure = VAR Columns_ = SELECTEDVALUE ( 'Columns'[Columns] ) RETURN SWITCH ( Columns_, "YTD", CALCULATE ( [YTD], Data[Net Performance] = "OK" ), CALCULATE ( [On Time], Data[Quarter] = Columns_ ) )3. Put 'Columns'[Columns] into "Axis" field and [Late Measure], [On Time Measure] into "Values" field to create a visual.
You can this post: Possible Analytics function: dynamic Total column in chart with USA States?
Not exactly the same, but the logic is the same.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
