Show a measure at a parent level and not at a child level

[Sanjay_Shah]

I have a matrix where the first field is Company, and below that there may be different fields like brands, categories etc.. I want to show the Company Rank by Sales, only for that row which has the company, and for no other rows. Can I do this without having to hard code all other fields using isfiltered or isinscope etc ?

[Wilson_]

Hi Sanjay_Shah,

The good news is you don't have to hardcode all the other fields using ISINSCOPE. You would only have to do it for the second level in the hierarchy; the second level in the hierarchy would be in scope for all the other levels under it too.

You could do something like:

Company Rank by Sales =
VAR Rnk = RANKX ( ALL (Table1[Company] ), [Company Sales], , DESC )
VAR Result = SWITCH (
    TRUE(),
    ISINSCOPE ( Table1[Second Hierarchy Level] ), BLANK(),
    Rnk
)

RETURN Result

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[Sanjay_Shah]

Hi Wilson

But this requires that i know the second level. What if there is a field parameter where the user selects any level ?

Sanjay

[Sanjay_Shah]

Hi Wisdom Wu

Thanks, but this does not satisfy my requirement.  the rank should come only against the company row, and no where else. We can always use the isfiltered and check for all the other dimension attributes. But what i am asking is a solution where we dont have to hard code other dimension attributes. is there a generic solution which checks for the row on which the company code is and gives a rank only for that row and no other row.

Sanjay

[v-jiewu-msft]

Hi @Sanjay_Shah ,

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate the sales.

Company Sales = CALCULATE(SUM('Table'[Sales]), ALLEXCEPT('Table', 'Table'[Company]))

3.Create the new measure to rank the company based on sales.

Rank = RANKX(ALL('Table'[Company]), [Company Sales], , DESC)

4.Drag the measure into the matrix visual.

5.The result is shown below.

Best Regards,

Wisdom Wu

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