Getting % of total when applying Top 10 filter

[Aevans1uk]

I have a list of Suppliers and Schedules, with different statuses. E.g. Some are 'On Time' some are 'Not on Time'.

I have 3  measures that count the Schedule References to give me a 1. Total number of Schedules, 2. A count of those Schedules that are 'On Time' and 3. A count of those Schedules that are 'Not On Time'.

I need to display the Top 10 Suppliers by Schedules that are 'Not On Time', but calculate their % of 'Not on Time' based on the total number of Schedules, not just their own total number of Schedules.  Anyone help please?

Thanks

[dk_dk · Accepted solution]

Hi @Aevans1uk ,

Try something like this:

% of Not on Time =
VAR notOnTime = [ScheduleCountNotOnTime] //your third measure
VAR totalSchedule = CALCULATE([ScheduleCount],REMOVEFILTERS(Suppliers)) //your first measure, and the table that contains the suppliers

RETURN
DIVIDE(notOnTime,totalSchedule)

and then format the measure as %.

I hope this helps, let me know if you have any questions.

[dk_dk · Accepted solution]

Hi @Aevans1uk ,

Try something like this:

% of Not on Time =
VAR notOnTime = [ScheduleCountNotOnTime] //your third measure
VAR totalSchedule = CALCULATE([ScheduleCount],REMOVEFILTERS(Suppliers)) //your first measure, and the table that contains the suppliers

RETURN
DIVIDE(notOnTime,totalSchedule)

and then format the measure as %.

I hope this helps, let me know if you have any questions.

[Aevans1uk]

Hi, thank you so much - it appears to be working, except I forgot about one thing.

There are also Months in the table. My Top 10 are filtered by the previous two months also, How would I build that into the total calculation please?

Thank you

[dk_dk · Accepted solution]

You can add a third argument to the CALCULATE and write REMOVEFILTERS(Calendar) if you want the ratio to be calculated agains the total number of all time schedules, not just the the last 2 months.

The exact command you write depends a bit on what tables you have in your model and how are they connected. Is the month in a separate date table? Or is it part of the suppliers or schedules tables?

Have a look at the CALCULATE function definition, and then REMOVEFILTERS, KEEPFILTERS, ALLEXCEPT, and other filter functions, here or here.
Depending on your data and the exact calculation you want, you may need to use them differently, or use multiple CALCULATEs to get to a specific result.

If you are having trouble with achieving your goal, it would be great if you can provide either a pbix file or a sample of the data you have (tables, relationships, a few rows of data), and a plaintext definition of what you want your calculated measure to show, and I will try my best to help!


Best regards,
Daniel