Calculate count of percentage and place in a

[Anonymous]

Hi All,

I have prepared the result of my dataset in excel and am unable to do so in PBI. Below is the required result:

1. I have 4 categories- A, B, C, D

2. These categories are further divided into Worst, Bad, Good, Very Good- This will go on X-axis 

3. On Y-axis, there are 4 categories of %- <10%, 10-20%, 20-30%

Now for every category in point 1 A, B, etc, I will calculate total % in category 2 and enter the count in category 3. 

For eg, if for A, B the individual calculation for "Worst" come to be <10%, then it should enter the count as 2 in that bracket.

Worst: A=9%, B=6%, C=15%

Bad: A= 15%, B=5%, C=16%

Below is the excel version and I need to create a matrix in PBI. Somehow I am unable to segregate based on the count of percentages.

40-100
20-40
10-20%	1	2
<10%	2	1
	Worst	Bad	Good	Very Good

Thanks!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a column

Category 3 =
SWITCH (
    TRUE (),
    Table1[VALUE] < 0.1, "<10%",
    Table1[VALUE] < 0.2
        && Table1[VALUE] >= 0.1, "10%-20%",
    Table1[VALUE] < 0.4
        && Table1[VALUE] >= 0.2, "20%-40%",
    Table1[VALUE] <= 1
        && Table1[VALUE] >= 0.4, "40%-100%"
)

Create a measure

Measure = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[category2],Table1[Category 3]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.