Grouping based on Topn - Not adding together

DAX DAX Calculations
1

Hi PowerBi Friends,

I have a table and grouped it based on TopN. However, the same entity (company name) is splitting into other TopN where it shouldn’t be. e.g Company XYZ was on Top10 and also showing in the Top50 as well. It should be summed together and only be placed on 1 particular TopN. How can I do this? Attached is my measure.

    Total Invoice By Rank Group = 
    CALCULATE(
    [Total Invoice],
    FILTER(
        FactFinalisedInvoicesDetailed,
        COUNTROWS(
            FILTER(
                'Customer Rank',
                [Rank by Group Owner and Invoice Type] >= 'Customer Rank'[Min Rank]
                    && [Rank by Group Owner and Invoice Type] <= 'Customer Rank'[Max Rank]
                )
            ) > 0        
        )
    )

image
image

2

@ronald_balza Share your Power BI file.

3

@AntrikshSharma there you go.

4

Hello @ronald_balza,

Thank You for posting your query onto the Forum.

Firstly, there was a tie between two of the Group Owners. See the screenshot provided below -

Ties Between Group Owner
Ties Between Group Owner565×656 38.9 KB

So in this case, we’ll be required to break ties between them. @AntrikshSharma has already created a fantastic video which showcases how to break ties. So using that technique firstly I broke the ties and than calculated the Owner Rank again. Below are the formulas provided for the reference -

1). To Break Ties -

Alphabetical Rank = 
VAR _Current_Owner = SELECTEDVALUE( Data[Group Owner] )

VAR _Result = 
COUNTROWS(
    FILTER( 
        ALL( Data[Group Owner] ) , 
        Data[Group Owner] <= _Current_Owner ) )

RETURN
_Result

2). Ranking Of Owners -

Owner Rank = 
IF( ISINSCOPE( Data[Group Owner] ) , 
    RANKX( ALL( Data[Group Owner] ) , [Total Invoice] + DIVIDE( [Alphabetical Rank] , 100 ) , , DESC ) ,
    0 )

Now, create a measure for the Grouping of Owners. Below is the measure provided for the reference -

Total Invoice By Rank Group = 
CALCULATE( [Total Invoice] ,
    FILTER( VALUES( Data[Group Owner] ) , 
        COUNTROWS(
            FILTER(
                'Group' ,
                [Owner Rank] >= 'Group'[Min] &&
                [Owner Rank] <= 'Group'[Max] ) ) > 0 ) )

Now, you’ll see that you’ll get the appropriate results. Below is the screenshot provided for the reference -

Output
Output909×761 27.9 KB

I’m also attaching the working of the PBIX file for the reference as well as providing the link of the video which @AntrikshSharma has created.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

sampledata - Harsh.pbix (37.0 KB)

1 Like
5

Thanks for the help Harsh! You are awesome. Will definetly learn from it. Cheers!

6

Hello @ronald_balza,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh