Calculate percentage from unpivoted data

[aaronvincentnz]

Hello

Apologies if this has been asked before (I had a search but couldn't quite get the existing solutions to fit my query).

I have a column (let's call is Service Level) where the options are 'Good', 'Bad', 'N/A' and I have a column for Unique ID. I have unpivoted my original data set (to calculate a separate formula) so am now left with duplicate unique ID's and duplicate Service Level responses.

What formula do I need to use to calculate the % of 'Good', 'Bad', 'N/A' by unique ID given that the data is unpivoted?

Thanks in advance

[amitchandak]

@aaronvincentnz ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[aaronvincentnz]

Unique ID	Service Level
1054	Good job
1054	Good job
1054	Good job
1054	Good job
1054	Good job
1054	Good job
1054	Good job
1074	Bad job
1074	Bad job
1074	Bad job
1074	Bad job
1074	Bad job
1074	Bad job
1156	Don't know
1156	Don't know
1156	Don't know
1156	Don't know
1156	Don't know
1156	Don't know
1156	Don't know
1221	Good job
1221	Good job
1221	Good job
1221	Good job
1255	Good job
1255	Good job
1255	Good job
1255	Good job
1266	Bad job
1266	Bad job
1266	Bad job
1266	Bad job
1266	Bad job
1266	Bad job
1266	Bad job

[ibarrau · Accepted solution]

Hi. Create a visualization with Service Level and a measure like this:

% of unique =
VAR _num = DISTINCTCOUNT(Table[Unique])
VAR _den = CALCULATE(
    DISTINCTCOUNT(Table[Unique])
    , ALL(Table)
)
RETURN
DIVIDE ( _num , _den )

After that you can format the measure with % symbol.

Hope that helps, 

[aaronvincentnz]

The formula works, but the result is incorrect - I know what the result is meant to be based on checking via a pivot table from the original source data....it looks like I'd get the same result if I use the % of grand total function within the Values field without using any sort of formula...

[v-shex-msft]

Hi @aaronvincentnz,

It seems like you share the duplicate sample data with the same status, can you please share some data with raw table structures and expected result to test?

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[aaronvincentnz]

Here's a sample of the data (apologies for the messy formatting)

The 'don't know' is equivalent to N/A from the original info in my question

Data Unpivoted
Unique ID	Service Level
1054	Good job
1054	Good job
1054	Good job
1074	Bad job
1074	Bad job
1074	Bad job
1156	Don't know
1156	Don't know
1156	Don't know
1221	Good job
1221	Good job
1221	Good job
1221	Good job
1255	Good job
1266	Bad job
1266	Bad job

Original Data
Unique ID	Service Level
1054	Good job
1074	Bad job
1156	Don't know
1221	Good job
1255	Good job
1266	Bad job

Expected Result
Service Level	Actual Counts	% of Service Level
Good Job	3	50%
Bad Job	2	33%
Don't Know	1	16%
Total	6	100%

[mahoneypat]

Here is a measure expression that gets the result shown in a table visual with the Service Level column.  Replace Service with your actual table name.

% of Service Level =
VAR countsatlevel =
    DISTINCTCOUNT ( Service[Unique ID] )
VAR countsoverall =
    CALCULATE (
        DISTINCTCOUNT ( Service[Unique ID] ),
        ALL ( Service[Service Level] )
    )
RETURN
    DIVIDE (
        countsatlevel,
        countsoverall
    )

Regards,

Pat

[aaronvincentnz]

That's worked, thanks (sorry for the late reply, been on leave). Just have to work out how to make the legend not display in alphabetical order lol...