cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Dog Established Member
Established Member

Display Ranked Totals

Hi All, 

 

I'm having some issues trying to retrieve a total value for items that are ranked. 

 

on my report page I have a numerical slicer which shows the top x summed sales by office. 

the sum of sales is displayed using a bar graph. 

I am using RANKX to determine the rank number on the selected office locations and this is all working perfectly. 

what my client would like to see is the total sum of Sales for the offices that appear in the bar graph , so the sum of the sales for the top 10 locations.

they would also like to see the sum of the other locations. 

 

the bit I am struggling with is the rankx function is performed on the office locations, which is possible because these make up the axis on the bar graph. However, when I try to retrieve the sum of these to display in a single measure I am unable due to the office locations not being filtered within the card. 

 

My RANKX measure is simply doing this. 

SalesRanks:=RANKX ( FILTER ( ALLSELECTED ( Office[Location Title] ), CALCULATE(SumOfSalesYTD] )), CALCULATE(SumOfSalesYTD] ),, ASC, DENSE )

 

any help would be appreciated. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Dog Established Member
Established Member

Re: Display Ranked Totals

Looks like I've found a way to do this. 

I'll post just in case someone can think of a more straight forward way or has the same problem. 

 

in a nutshell, I calculate the table using summarize, filter this down and then perform the ranking and summing of sales using add columns. 

I wrap this all in a SUMX to interate through values and return the result. 

 

testtableoutput :=
VAR SelectedNo =
     IF (
          ISFILTERED ( 'TopN Options'[TopN] ),
          VALUES ( 'TopN Options'[TopN] ),
          BLANK ()
     )
VAR fTable =
     IF (
          ISBLANK ( SelectedNo ),
          CALCULATE ( SUM ( Office[SumOfSalesYTD] ) ),
          SUMX (
               FILTER (
                    ADDCOLUMNS (
                         SUMMARIZE ( ALLSELECTED ( Office ), Office[DWHId], Office[Location Title] ),
                         "MyValue", CALCULATE ( SUM ( Office[SumOfSalesYTD] ) ),
                         "MyRank", RANKX (
                              ALLSELECTED ( Office ),
                              CALCULATE ( SUM ( Office[SumOfSalesYTD] ) ),
                         ,
                         DESC,
                              DENSE
                    )
               ),
               [MyRank] <= SelectedNo
          ),
          [MyValue]
     )
)
RETURN
fTable

View solution in original post

1 REPLY 1
Dog Established Member
Established Member

Re: Display Ranked Totals

Looks like I've found a way to do this. 

I'll post just in case someone can think of a more straight forward way or has the same problem. 

 

in a nutshell, I calculate the table using summarize, filter this down and then perform the ranking and summing of sales using add columns. 

I wrap this all in a SUMX to interate through values and return the result. 

 

testtableoutput :=
VAR SelectedNo =
     IF (
          ISFILTERED ( 'TopN Options'[TopN] ),
          VALUES ( 'TopN Options'[TopN] ),
          BLANK ()
     )
VAR fTable =
     IF (
          ISBLANK ( SelectedNo ),
          CALCULATE ( SUM ( Office[SumOfSalesYTD] ) ),
          SUMX (
               FILTER (
                    ADDCOLUMNS (
                         SUMMARIZE ( ALLSELECTED ( Office ), Office[DWHId], Office[Location Title] ),
                         "MyValue", CALCULATE ( SUM ( Office[SumOfSalesYTD] ) ),
                         "MyRank", RANKX (
                              ALLSELECTED ( Office ),
                              CALCULATE ( SUM ( Office[SumOfSalesYTD] ) ),
                         ,
                         DESC,
                              DENSE
                    )
               ),
               [MyRank] <= SelectedNo
          ),
          [MyValue]
     )
)
RETURN
fTable

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors