Forum Discussion

pr92's avatar
pr92
Frequent Visitor
1 year ago
Solved

Divide 2 values from same column and separate rows in measure

Hello,

 

I have the following table in my model :

 

Category1Category2Number of tickets
A15FEMALE166960
A15MALE188816
A15Total654229
A15UNKNOWN26456
A6FEMALE226964
A6MALE6242260
A6Total66466
A6UNKNOWN66246
A52FEMALE661456
A52MALE2690604
A52Total486091
A52UNKNOWN441060
A54FEMALE1500896
A54MALE2526514
A54Total456656
A54UNKNOWN512646

 

I need to display the Top Category2 based on the following value : 

 

For each value in Category1 : 

Number of tickets of Category2 /  Number of tickets of Category2 WHERE Category1 = Total

 

 

 

Category1Category2Number of ticketsColonne1
A15FEMALE16696026%
A15MALE18881629%
A15Total654229100%

For example : Here for A15, I need to display MALE (29%)

 

I did the following measure : 

 

%_Tickets_Category1 =

VAR _total = CALCULATE(
    SUM(Table[NumberTickets]),  
    FILTER(
        ALL(Table),  Table[Category2] = "Total")
    )
VAR _sum = SUM(Table[NumberTickets])
RETURN DIVIDE(_sum, _total)
 
On the page I have a segment that filters by Category1
 
It works on the page if I display ALL values in a table but not when I apply filter Top 1 :
 

 

Any help would  be appreciated

Thank you

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi pr92 -The measure you've written can be adapted to handle both the division and the correct filtering within the Top N filter context. to calculate the percentage of tickets per Category2 use below one

    %_Tickets_Category1 =
    VAR _total = CALCULATE(
    SUM(Table[Number of tickets]),
    FILTER(ALL(Table),
    Table[Category2] = "Total" &&
    Table[Category1] = SELECTEDVALUE(Table[Category1])
    )
    )
    VAR _sum = SUM(Table[Number of tickets])
    RETURN DIVIDE(_sum, _total)

     

    Once you have the percentage measure (%_Tickets_Category1), you need to apply the Top N filter to display only the Category2 with the highest percentage for each Category1.

    In the Filters pane, click on the Category2 field.Change the filter type to Top N.Enter 1 as the Top N value.
    Drag the measure %_Tickets_Category1 into the "By Value" field.
    Apply the filter, and Power BI will now display only the top Category2 with the highest percentage for each Category1.

    Hope this works 

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi pr92 -The measure you've written can be adapted to handle both the division and the correct filtering within the Top N filter context. to calculate the percentage of tickets per Category2 use below one

    %_Tickets_Category1 =
    VAR _total = CALCULATE(
    SUM(Table[Number of tickets]),
    FILTER(ALL(Table),
    Table[Category2] = "Total" &&
    Table[Category1] = SELECTEDVALUE(Table[Category1])
    )
    )
    VAR _sum = SUM(Table[Number of tickets])
    RETURN DIVIDE(_sum, _total)

     

    Once you have the percentage measure (%_Tickets_Category1), you need to apply the Top N filter to display only the Category2 with the highest percentage for each Category1.

    In the Filters pane, click on the Category2 field.Change the filter type to Top N.Enter 1 as the Top N value.
    Drag the measure %_Tickets_Category1 into the "By Value" field.
    Apply the filter, and Power BI will now display only the top Category2 with the highest percentage for each Category1.

    Hope this works