Divide 2 values from same column and separate rows in measure

[pr92]

Hello,

I have the following table in my model :

Category1	Category2	Number of tickets
A15	FEMALE	166960
A15	MALE	188816
A15	Total	654229
A15	UNKNOWN	26456
A6	FEMALE	226964
A6	MALE	6242260
A6	Total	66466
A6	UNKNOWN	66246
A52	FEMALE	661456
A52	MALE	2690604
A52	Total	486091
A52	UNKNOWN	441060
A54	FEMALE	1500896
A54	MALE	2526514
A54	Total	456656
A54	UNKNOWN	512646

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

Category1	Category2	Number of tickets	Colonne1
A15	FEMALE	166960	26%
A15	MALE	188816	29%
A15	Total	654229	100%

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

[rajendraongole1 · Accepted solution]

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