Show same Top N categories for different visualisations

[meg222]

Hi,

I have a visualisation below which shows the top 3 answers for the category which is selected in a slicer. The "% A4" is a measure calculated like this: 

% A4 = CALCULATE(DISTINCTCOUNT(A4[UNIQUE_ID]),A4[Response] = "Yes") / DISTINCTCOUNT(A4[UNIQUE_ID])

I have another bar chart which shows the same thing but doesn't get filtered by the slicer so that it shows the total across all categories. However I need this bar chart to show the same top 3 as what is showing in the first bar chart, even though it doesn't get filtered by the slicer. So for the below chart I need the first bar to be "At home" and show 62% the second bar to be "At work, college" and show 11% and third bar to be "In the car" and show 15%

The slicers on these visuals are all located in a "Demographics" table which is linked one to many to the A4 table. There are other slicers (all located in the demographics table) which need to work on both of the above bar charts. It is just the slicer for category which should only filter the first chart.

I need the bars to be the same so that I can layer the two charts on top of each other like below, which won't make sense if they are showing a different top 3!

Any help appreciated!

[v-rzhou-msft]

Hi @meg222 ,

If you turn off the interaction, it will be difficult for us to to use the filter in visual 1 in our calculation. Here I suggest you to inactive the relationship between "Demographics" table and "A4" table.

% A4 for Visual1 = 
VAR _DIVIDE =
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( A4[UNIQUE_ID] ),
            A4[Response] = "Yes",
            USERELATIONSHIP ( A4[A4], Demographics[Category] )
        ),
        CALCULATE (
            DISTINCTCOUNT ( A4[UNIQUE_ID] ),
            USERELATIONSHIP ( A4[A4], Demographics[Category] )
        )
    )
RETURN
    _DIVIDE

% A4 for Visual2 =
VAR _LIST =
    VALUES ( Demographics[Category] )
RETURN
    IF (
        MAX ( A4[A4] ) IN _LIST,
        DIVIDE (
            CALCULATE ( DISTINCTCOUNT ( A4[UNIQUE_ID] ), A4[Response] = "Yes" ),
            DISTINCTCOUNT ( A4[UNIQUE_ID] )
        )
    )

Do not inactive the interaction from slicer in viusal2, and use top3 [% A4 for  Visual1] to filter both visuals.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[meg222]

Hi Rico Zhou,

Thank you for taking a look! I'm having some trouble with making the relationship between the demographics table and A4 inactive - it then isn't filtering the first visual for the selected category. 

Also my demographics table is a bit different - it doesn't contain the same categories as are in A4 - it has other categories which need to be able to filter both visual 1 and visual 2. I need something which looks like the below with a slicer for Podcast and a slicer for Gender (which are in the demograhics table)

I've attached a sample pbix file here:

https://we.tl/t-AhpqUeSBqE 

Thanks,

Meg

[amitchandak]

@meg222 , I doubt the second visual. But you can have two top3 measure using topn and use those in visual

example

TOP3 Category net = CALCULATE([Net], TOPN(3,ALLSELECTED('Item'[Category]), [Net],DESC), values('Item'[Category]))

TOP3 Category gross= CALCULATE([gross], TOPN(3,ALLSELECTED('Item'[Category]), [gross],DESC), values('Item'[Category]))

TOPN: https://youtu.be/QIVEFp-QiOk

Power BI- TOPN with Others- https://youtu.be/I_TY4hVlzAE

[meg222]

Thanks for your reply.

The first visual is working fine and showing what I need. It is the second visual I am having problems with - do you think it is possible to make it so that the second visual shows the same top 3 answers as the first visual?