Create a Measure that calculates the % of Items that have a certain value in another measure

[aallman]

Hello!

I currently have 3 measures to track our on time % for each of our Key Accounts. Here is a quick example of the output of those:

Account	%OTIF	%OTD	%OTD$
Alpha	93%	91%	92%
Beta	99%	100%	99%
Gamma	98%	94%	96%

What I need to do now is create 3 separate measures that calculate the % of ACCOUNTS that have at least 95% in each of my current measures. So in the example above I would want the following results:

%OTIF>95 = 67%

%OTD>95 = 33%

%OTD$>95 = 67%

How can I accomplish this in a way that my new measures will change based on any filters applied on my page, similar to the way my current measures work?

Thanks in advance!

[rohit1991 · Accepted solution]

Hi @aallman ,

You don’t need to mess with calculated columns or calculated tables, measures will give you the flexibility you need so your results always reflect the filters and slicers on your page.

Let’s say you want to calculate the percentage of accounts where, for example, %OTIF is at least 95%. Just create a measure like this:

% Accounts with OTIF >= 95 =
VAR Accounts = VALUES(Accounts[Account])  // Replace with your actual table and column
VAR AccountsAbove95 =
    FILTER(
        Accounts,
        [%OTIF] >= 0.95
    )
RETURN
    DIVIDE(COUNTROWS(AccountsAbove95), COUNTROWS(Accounts))

If you want to do the same for %OTD or %OTD$, just swap in your respective measure. This logic stays dynamic, so whatever filters or slicers you put on the page will automatically flow through.

Want to check if ALL three measures are at least 95% for the same account? Just extend the logic:

% Accounts where All Measures >= 95 =
VAR Accounts = VALUES(Accounts[Account])
VAR AccountsAbove95 =
    FILTER(
        Accounts,
        [%OTIF] >= 0.95 &&
        [%OTD] >= 0.95 &&
        [%OTD$] >= 0.95
    )
RETURN
    DIVIDE(COUNTROWS(AccountsAbove95), COUNTROWS(Accounts))

[aallman]

Amazing, this worked exactly how I needed it to! Thank you so much for your help!

[vojtechsima]

Hello, @aallman ,
you can do something like this:

over 95 = 
var accounts = VALUES(tbl[letter])
var allAccounts = COUNTROWS(accounts)
var percentForAccount = 
ADDCOLUMNS(
    accounts,
    "@measure1", [%],
    "@measure2", [%],
    "@measure3", [%]
)
var filteredAccounts = FILTER( percentForAccount, [@measure1] >= 0.95 && [@measure2] >= 0.95 && [@measure3] >= 0.95 )

var result = DIVIDE( COUNTROWS( filteredAccounts ) , allAccounts )

return result

The [%] represent actual measure 1 - 3.

[aj1973]

Hi @aallman 

Not sure how your dataset is built to get the view you are sharing but basicaly you will need to add conditional columns for each variable (OTIF, OTD and OTD$) Where you return 1 if value is > 95 else 0 and then create a measure to DIVIDE(SUM(NewColumn(OTIF)) , Number of Accounts).

As an Example : For %OTIF you have 2 values > %95 out of 3 Accounts = 67%

[aallman]

I'm sorry I should have mentioned more about my data structure. The underlying table is sales orders/line items each of which can either be On Time or Late. My current measures for OTIF,OTD, and OTD$ are doing calculations looking for the count of orders under that Account Name that are On Time divided by the total count of orders under that Account Name.

I don't think a calculated column will work because of this.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[aj1973]

No, but a calculated table or sumarized columns can do the job.

May be sharing a sample of your dataset is better and faster for an accurate return