Filtered categorical measure yields incorrect output

[mba]

Hi!

I calculated a measure that first ranks companies based on the revenue generated, then splits them into Tiers (top 25% etc.). Further, I created a complementary measure that allows to filter the desired table based on this measure. Of course, to make it happen, I had to create a intermediary table specifically for this reason. However, it doesn't yield correct results in the end.

Let's start with a data as follows:

Company Name	Gross Revenue
Company A	100 000
Company B	80 000
Company C	70 000
Company D	60 000

The measure that groups into tiers based on revenue is defined as:

Company Tier Group (Based on Gross Revenue) = 

VAR CompaniesCount = CALCULATE(DISTINCTCOUNT(Products[CompanyName]), ALLSELECTED(Products))
VAR RANKING = RANKX
(
    ALLSELECTED(Products),
    CALCULATE
    (
        [Company Revenue (incl. VAT)],
        ALLEXCEPT
        (
            Products,
            Products[CompanyName], 
            Products[Country] // later filtering purposes
        )
    ),
    ,
    DESC,
    Dense
)

VAR TopPercentage = DIVIDE(RANKING, CompaniesCount)

RETURN
IF(ISINSCOPE(Products[CompanyName]),
    IF
    (
        TopPercentage <= 0.25,
        "Tier 1",
        IF
        (
            TopPercentage > 0.25 && TopPercentage <= 0.75,
            "Tier 2",
            "Tier 3"
        )
    )
)

and in the table, it works just fine:

Company Name	Gross Revenue	Group (Based on Revenue)
Company A	100 000	Tier 1
Company B	80 000	Tier 2
Company C	70 000	Tier 2
Company D	60 000	Tier 3

Normally, as in the table shown above, the groups seem to work just fine. To allow using the slicer based on this measure, I had to create an intermediary table (called 'Company Tier List') with just "Tier 1", "Tier 2" and "Tier 3" values next to the other column stipulating its order (to appear nicely in the slicer). The new measure created to allow filtering is defined as follows:

That table:

TIERS	ORDER
Tier 1	1
Tier 2	2
Tier 3	3

Measure:

Company Tier Group (Based on Gross Revenue) FILTER = 

VAR selectedTier = VALUES('Company Tier List'[Tier])
VAR currentTier = [Company Tier Group (Based on Gross Revenue)]

RETURN 
IF(currentTier IN selectedTier, 1, 0)

... and it worked just fine as well. Putting it all together, I have a table that goes: (no Tier selected in the slicer)

Company Name	Gross Revenue	Group (Based on Revenue)	Group Filter
Company A	100 000	Tier 1	1
Company B	80 000	Tier 2	1
Company C	70 000	Tier 2	1
Company D	60 000	Tier 3	1

If I select "Tier 2" on the slicer, I get:

Company Name	Gross Revenue	Group (Based on Revenue)	Group Filter
Company A	100 000	Tier 1	0
Company B	80 000	Tier 2	1
Company C	70 000	Tier 2	1
Company D	60 000	Tier 3	0

Then, if I want to display only these rows with value of 1 in "Group Filter", I add the visual filter to do so. What I expect is:

Company Name	Gross Revenue	Group (Based on Revenue)	Group Filter
Company B	80 000	Tier 2	1
Company C	70 000	Tier 2	1

What I actually get either an empty table or rows that have inconsistent Tiers.

If I do not impose that filter, I can see that either the Tier 1/2/3 and Group Filter 1/0 marks the rows correctly, also when I impose additional filters of time period and country. It is at the point when I need to show only these with Group Filter equal to 1 when it breaks. It shows more than it should for Tier 1 (that is more than 25% of rows) while putting the rest to Tier 2 and having no rows in Tier 3. It also depends on the number of rows in the period selected. If there are few of them, only filtering for Tier 1 shows all rows, whereas Tier 2 and Tier 3 shows none.

Here are some screenshots that visualise the steps, respectively:

... using slicer...

... after using the visual filter...

I will appreciate any solution to my problem as well as some other suggestions how the desired output may be reached.

[Ashish_Mathur]

Hi,

I cannot understand the logic for the calculated of tiers in the first screenshot.  Company A's revenue is 33% of the total revenue which is >25%.  Why then does it fall in tier 1?

[mba]

Hi @Ashish_Mathur ,

It's not about the percentage of a total, but about its rank divided by a number of companies. In case of Company A, it's ranked 1 out of 4, what yields 1/4 = 0.25 = 25%.

[lbendlin]

To allow using the slicer based on this measure

You cannot base slicers on measures. Best you can do is use what-if parameters.

[Ashish_Mathur]

Hi,

I cannot get your expected result.  However, you may refer to my measure in this PBI file and tweak the measures.  Sorry but could not help.

 

[Greg_Deckler]

@mba Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[mba]

Hi @Greg_Deckler ,

Thank you for the response and suggestions! I tried to make the issue more transparent via editing the initial post. I hope it is more clear to you now.