Running value as a % of Yearly Total

[gooranga1]

I have a table that has totals of deliveries per company per route per 30 minute window in the day as below. Each day has 48 timebands and I am using 24 of then from 22:00 to 10:00

I have a 100% Stacked Bar chart by year with a legend of the timeband counting the total operational deliveries. I also having a running total as a tooltip. the timebands are joined to a dim_timeband table.

Total Deliveries YTD = CALCULATE(sum(Fact_TimeBandAnalysis[Total Operations Deliveries]),FILTER(ALLSELECTED(Dim_TimeBand),Dim_TimeBand[time_band_id_bb]<=max(Dim_TimeBand[time_band_id_bb])))

this isn't really a ytd total it's the cumulative of the timebands in time order.

This works okay but I would really like to add another tooltip that also gave the running total value as a % of the total for the year as well under Total Deliveries YTD. Currently th e% is the percentage of that timeband but it would be nice to see what the cumulative is. so as we got to 07:00 - 07:29 we can see 98% of the deliveries have been made. You can get this by looking at the below chart left hand column but it would be nice to see in a tooltip as well.

I have tried a few things but I can't seem to get to a grand total for the year I can use to divide the running total by. I just can't figure if there is a way to build a measure that calculates it. I would want this to work also whilst filtering on the routes and company.

[v-huizhn-msft]

Hi @gooranga1,

I create the following sample table and try to reproduce your scenario.



And create a relationship betweem them.



I use create a measure like you.

Total Deliveries YTD = CALCULATE(sum(Fact_TimeBandAnalysis[Total Operations Deliveries]),FILTER(ALLSELECTED(Dim_TimeBand),Dim_TimeBand[Time_bb]<=max(Dim_TimeBand[Time_bb])))

Create a measure to calculate percentage for each Time band VS all the time.

percentage = Fact_TimeBandAnalysis[Total Deliveries YTD]/CALCULATE(SUM(Fact_TimeBandAnalysis[Total Operations Deliveries]),ALL(Fact_TimeBandAnalysis))

Please see the screenshot below. Select the Dim_TimeBand[TimeBrand] as Legend(notice the highlighted in red line) and measure as value, the percentage as Tooltips.



If this is not what you want, please share more details for further analysis.

If you have other issues, please feel free to ask.

Best Regards,

Angelia

[gooranga1]

Hi @v-huizhn-msft,

Yes that does work but there is another filter I need to be able to filter on the current year. So the formula does produce a % of total but its the % of the whole table not just the appropriate year.

However your solution did help me work out what I needed to do to make it work across years, thank you;

Percentage = [Total Deliveries YTD] / CALCULATE(sum(Fact_TimeBandAnalysis[Total Operations Deliveries]),FILTER(ALLSELECTED(Fact_TimeBandAnalysis),Fact_TimeBandAnalysis[Calendar Year]=max(Fact_TimeBandAnalysis[Calendar Year])))

ModifiedOriginal

[v-huizhn-msft]

Hi @gooranga1,

Have you resolved your issue? I am confusing with "another filter I need to be able to filter on the current year". Is there any chance to share your .pbix file for further analysis?

Best Regards,
Angelia

[gooranga1]

Hi @v-huizhn-msft,

Yes the issue is now resolved. I used your solution but then I added an extra filter to be able to filter on the year not the whole table!

Thanks for yoiur help!

[v-huizhn-msft]

Hi @gooranga1,

You want to create a slicer which only filter the year not the whole table? If it is, you should create a new table including year. If a slicer include a filed in one table, it will filter whole table in Power BI.

Best Regards,
Angelia