Group by and count one column to divide over different categories of the column - tooltip

[hayleedee]

I have a bar chart with counts by "ln" on the left and split by "ment" for each category of "ln"

I would like a tooltip which shows the proportion of the red to the green for each "ln" - however, my current calculation excludes the green when hovering over the red as of course the data within there is only for the red.

How can I create a measure which by passes that category while counting them seperately?

Thanks for your help.

[hayleedee · Accepted solution]

Thanks very much - that got me started in the right direction.

Here is my final solution that produced the ratio of red to green:

ratio_of_red2green = CALCULATE(COUNT('Final_dataset'[ln]), FILTER(ALLEXCEPT(Final_dataset,Final_dataset[ln]),Final_dataset[ment] = "RED"))/CALCULATE(COUNT(Final_dataset[ln]), FILTER(ALLEXCEPT(Final_dataset, Final_dataset[ln]), Final_dataset[ment] = "GREEN"))

Thanks all 👍

[v-yingjl]

Hi @hayleedee ,

You can create a measure like this to use it as tooltip:

percent =
CALCULATE (
    COUNT ( 'Table'[In] ),
    FILTER ( ALLEXCEPT ( 'Table', 'Table'[In] ), 'Table'[ment] = "green" )
)
    / CALCULATE ( COUNT ( 'Table'[In] ), ALLEXCEPT ( 'Table', 'Table'[In] ) )

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

[hayleedee]

Apologies, I realised I said proportion and I meant ratio.

But the above has worked to give me the proportion - however, I should have said the ratio of the green to the red. Ideally it would be the same value for both the red and green tooltip, but not necessary.

[amitchandak]

@hayleedee , Assuming column in the axis is In

divide([measure], calculate([measure], filter(allselected(Table), Table[In] = max(Table[In]))))