cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zaminamina
Advocate IV
Advocate IV

Rankx with measure doesn't seem to work correctly

Here's the link to pbix file: https://www.dropbox.com/s/9k0fth7qed7t33n/sample%20data.pbix?dl=0

On the "D: Top 10" tab, I have certain attributes listed in the table as well as calculated measures. I'm trying to get the row entries with top 10 "R3M error" values to be displayed in the top table regardless of the attributes listed in the table (region, month year, warehouse, account group, product name, sales person), but it should be based on the filters applied on the table. For example, if these filters, Region=E1, Month Year=Feb 2021, were applied, then the rank function should only rank all the "Final Table" entries that apply to those filters, based on the "R3M error" measure, and only the top 10 "R3M error" values should be displayed in the table.

I've tried using the top N filtering option, but when I do that, all my "LM ..." and "2PM ..." measures don't work. I think it's currently ranking each individual region, month year, warehouse, account group, product name, and sales person combination, but that's not what I want.

This is my measure for the rank: 

 

 

Over rank = 
RANKX(
    ALLSELECTED('Final Table'),
    [R3M Error],
    ,
    DESC,
    Skip
)

 

 

This is my measure for the R3M error and R3M requested and forecast:

 

 

R3M Error = [R3M Forecast]-[R3M Requested]

R3M Forecast = 
CALCULATE(
    SUM('Final Table'[Forecast Requested Volume]),
    DATESINPERIOD(
        'Date Table'[Date],
        LASTDATE('Date Table'[Date]),
        -3,
        MONTH
    )
)

R3M Requested = 
CALCULATE(
    SUM('Final Table'[Requested Volume]),
    DATESINPERIOD(
        'Date Table'[Date],
        LASTDATE('Date Table'[Date]),
        -3,
        MONTH
    )
)

 

 

Thank you!

1 ACCEPTED SOLUTION

Hi again @zaminamina 

I've had a further look and believe I've come up with a version of Over rank that works.

 

The reason it's a little bit complicated is that we have to mimic the behaviour of SUMMARIZECOLUMNS (the function that Power BI uses within the DAX query generated by the visual), without using SUMMARIZECOLUMNS ourselves. SUMMARIZECOLUMNS does not support evaluation within a context transition, which we would need if we were to use it in this measure.

 

The measure instead uses GENERATECROSSJOIN/SUMMARIZE to construct a table whose rows correspond to the rows of the table visual (including removing rows where all measures are blank). Also, in order to mimic Auto-exist behaviour, we can't allow 'Date Table' filters to filter 'Final Table' when deciding which combinations of values from 'Final Table' are included.

 

The table is constructed by CROSSJOINing distinct combinations from 'Final Table' (ignoring the 'Date Table' filters) and 'Date Table', then removing rows that have all-blank measures. The rank is then calculated using this table as the first argument of RANKX.

 

I put some comments in the code that hopefully make it clearer. Also, I have written a simpler version of the measure that I think will work just as well. The simpler version assumes that [R3M Error] will be nonblank in every row of the table visual.

 

Full version:

 

Over rank = 
VAR RankingTable =
    CALCULATETABLE (
        GENERATE (
            -- The CROSSJOIN (...) code produces the set of rows that would be included in the visual
            -- if no measures were included (i.e. before removing rows with all measures blank. 
            CROSSJOIN (
                -- Summarize columns of 'Final Table'.
                -- Filters on 'Date Table' are removed using REMOVEFILTERS, to mimic Auto-exist behaviour:
                -- Auto-exist means the values from 'Final Table' are not filtered by 'Date Table'
                -- for the purpose of determining combinations to appear in the visual.
                CALCULATETABLE (
                    SUMMARIZE (
                        'Final Table',
                        'Final Table'[Account Group],
                        'Final Table'[Salesperson],
                        'Final Table'[Region/Forecasting Entity],
                        'Final Table'[Warehouse],
                        'Final Table'[Product Name]
                    ),
                    REMOVEFILTERS ( 'Date Table' )
                ),
                -- Summarize columns of 'Date Table'.
                -- Since 'Final Table' doesn't filter 'Date Table', we don't need REMOVEFILTERS here.
                SUMMARIZE (
                    'Date Table',
                    'Date Table'[Month Year],
                    'Date Table'[Year Month Number]
                )
            ),
            -- Evaluate all measures included in the visual.
            -- This could be reduced to a minimal set of measures.
            -- In sample data model, [R3M Error] could be sufficient.
            VAR Measure_Error = [Error]
            VAR Measure_Forecast_Requested = [Forecast Requested]
            VAR Measure_Requested = [Requested]
            VAR Measure_LM_Forecast = [LM Forecast]
            VAR Measure_LM_Requested = [LM Requested]
            VAR Measure_2PM_Forecast = [2PM Forecast]
            VAR Measure_2PM_Requested = [2PM Requested]
            VAR Measure_R3M_Forecast = [R3M Forecast]
            VAR Measure_R3M_Requested = [R3M Requested]
            VAR Measure_R3M_Error = [R3M Error]
            VAR Include_Row =
                -- Only include rows where at least one measure is nonblank
                -- (i.e. where not all measures are blank)
                NOT (
                    ISBLANK ( Measure_Error ) && ISBLANK ( Measure_Forecast_Requested )
                        && ISBLANK ( Measure_Requested )
                        && ISBLANK ( Measure_LM_Forecast )
                        && ISBLANK ( Measure_LM_Requested )
                        && ISBLANK ( Measure_2PM_Forecast )
                        && ISBLANK ( Measure_2PM_Requested )
                        && ISBLANK ( Measure_R3M_Forecast )
                        && ISBLANK ( Measure_R3M_Requested )
                        && ISBLANK ( Measure_R3M_Error )
                )
            RETURN
                -- Add the [R3M Measure] value to the table, but exclude the row if all measures are blank.
                FILTER ( ROW ( "@R3M Error", Measure_R3M_Error ), Include_Row )
        ),
        -- Use ALLSELECTED to ensure RankingTable is constructed in the overall filter context of the visual
        ALLSELECTED ()
    )
RETURN
    -- Finally, return the rank of [R3M Error] in current filter context, compared with all values in RankingTable
    RANKX ( RankingTable, [@R3M Error], [R3M Error], DESC, SKIP )

 

 

Simplified version:

 

Over rank = 
VAR RankingTable =
    CALCULATETABLE (
        GENERATE (
            CROSSJOIN (
                CALCULATETABLE (
                    SUMMARIZE (
                        'Final Table',
                        'Final Table'[Account Group],
                        'Final Table'[Salesperson],
                        'Final Table'[Region/Forecasting Entity],
                        'Final Table'[Warehouse],
                        'Final Table'[Product Name]
                    ),
                    REMOVEFILTERS ( 'Date Table' )
                ),
                SUMMARIZE (
                    'Date Table',
                    'Date Table'[Month Year],
                    'Date Table'[Year Month Number]
                )
            ),
            VAR Measure_R3M_Error = [R3M Error]
            VAR Include_Row =  NOT ISBLANK ( Measure_R3M_Error )
            RETURN
                FILTER ( ROW ( "@R3M Error", Measure_R3M_Error ), Include_Row )
        ),
        ALLSELECTED ()
    )
RETURN
    RANKX ( RankingTable, [@R3M Error], [R3M Error], DESC, SKIP )

 

Kind regards,

Owen 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

5 REPLIES 5
OwenAuger
Super User
Super User

Hi @zaminamina 

There is no way to create a rank measure that adapts to whatever level of detail of the fields included in the table visual (not yet, anyway).

 

However, you can write a measure that is defined based on a specific level of detail, using the fields currently in your table visual.

 

Here is an example for Over rank, that should work with the first table on the "D: Top 10" page:

Over rank =
VAR RankingTable =
    CALCULATETABLE (
        ADDCOLUMNS (
            CROSSJOIN (
                SUMMARIZE (
                    'Final Table',
                    'Final Table'[Region/Forecasting Entity],
                    'Final Table'[Warehouse],
                    'Final Table'[Account Group],
                    'Final Table'[Product Name],
                    'Final Table'[Salesperson]
                ),
                VALUES ( 'Date Table'[Month Year] )
            ),
            "@R3M Error", [R3M Error]
        ),
        ALLSELECTED ()
    )
RETURN
    RANKX (
        RankingTable,
        [@R3M Error],
        [R3M Error],
        DESC,
        SKIP
    )

The measure is a bit long-winded, as it has to explicitly list the fields by which the visual is grouped, in order to create a table to calculate ranks against.

 

One thing to note: because of the definition of [R3M Error] which looks back over 3 months, the dates for which the measure is nonblank don't have to correspond to combinations that exist in 'Final Table'. This is the reason 'Date Table'[Month Year] is not included in SUMMARIZE, but instead VALUES ( 'Date Table'[Month Year] ) is cross-joined with SUMMARIZE (...).

 

There could well be a way to simplify this measure, but I haven't come up with it yet!

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Hi Owen,

 

Thanks for the speedy response!

 

I tried the measure out, but I'm seeing an issue where if the "Error" column is blank, the "Over rank" function tends to repeat the ranking for those. How can we prevent this, cos it doesn't matter if "error" is blank or not, the "over rank" should just rank based on "R3M error". For the image below, I've selected the slicers: Region - E1, Month Year - Dec 2020.

 

zaminamina_0-1617244212249.png

 

Thanks for the reply @zaminamina .

Agreed, my measure is clearly not working as intended in that case!

The measure was intended to give a valid rank for the combinations of grouping fields that exist in the table, and the blank values for the [Error] measure do appear to be a clue.

 

I must have made a logical error somewhere along the way. I'll take a further look and get back to you.

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Hi again @zaminamina 

I've had a further look and believe I've come up with a version of Over rank that works.

 

The reason it's a little bit complicated is that we have to mimic the behaviour of SUMMARIZECOLUMNS (the function that Power BI uses within the DAX query generated by the visual), without using SUMMARIZECOLUMNS ourselves. SUMMARIZECOLUMNS does not support evaluation within a context transition, which we would need if we were to use it in this measure.

 

The measure instead uses GENERATECROSSJOIN/SUMMARIZE to construct a table whose rows correspond to the rows of the table visual (including removing rows where all measures are blank). Also, in order to mimic Auto-exist behaviour, we can't allow 'Date Table' filters to filter 'Final Table' when deciding which combinations of values from 'Final Table' are included.

 

The table is constructed by CROSSJOINing distinct combinations from 'Final Table' (ignoring the 'Date Table' filters) and 'Date Table', then removing rows that have all-blank measures. The rank is then calculated using this table as the first argument of RANKX.

 

I put some comments in the code that hopefully make it clearer. Also, I have written a simpler version of the measure that I think will work just as well. The simpler version assumes that [R3M Error] will be nonblank in every row of the table visual.

 

Full version:

 

Over rank = 
VAR RankingTable =
    CALCULATETABLE (
        GENERATE (
            -- The CROSSJOIN (...) code produces the set of rows that would be included in the visual
            -- if no measures were included (i.e. before removing rows with all measures blank. 
            CROSSJOIN (
                -- Summarize columns of 'Final Table'.
                -- Filters on 'Date Table' are removed using REMOVEFILTERS, to mimic Auto-exist behaviour:
                -- Auto-exist means the values from 'Final Table' are not filtered by 'Date Table'
                -- for the purpose of determining combinations to appear in the visual.
                CALCULATETABLE (
                    SUMMARIZE (
                        'Final Table',
                        'Final Table'[Account Group],
                        'Final Table'[Salesperson],
                        'Final Table'[Region/Forecasting Entity],
                        'Final Table'[Warehouse],
                        'Final Table'[Product Name]
                    ),
                    REMOVEFILTERS ( 'Date Table' )
                ),
                -- Summarize columns of 'Date Table'.
                -- Since 'Final Table' doesn't filter 'Date Table', we don't need REMOVEFILTERS here.
                SUMMARIZE (
                    'Date Table',
                    'Date Table'[Month Year],
                    'Date Table'[Year Month Number]
                )
            ),
            -- Evaluate all measures included in the visual.
            -- This could be reduced to a minimal set of measures.
            -- In sample data model, [R3M Error] could be sufficient.
            VAR Measure_Error = [Error]
            VAR Measure_Forecast_Requested = [Forecast Requested]
            VAR Measure_Requested = [Requested]
            VAR Measure_LM_Forecast = [LM Forecast]
            VAR Measure_LM_Requested = [LM Requested]
            VAR Measure_2PM_Forecast = [2PM Forecast]
            VAR Measure_2PM_Requested = [2PM Requested]
            VAR Measure_R3M_Forecast = [R3M Forecast]
            VAR Measure_R3M_Requested = [R3M Requested]
            VAR Measure_R3M_Error = [R3M Error]
            VAR Include_Row =
                -- Only include rows where at least one measure is nonblank
                -- (i.e. where not all measures are blank)
                NOT (
                    ISBLANK ( Measure_Error ) && ISBLANK ( Measure_Forecast_Requested )
                        && ISBLANK ( Measure_Requested )
                        && ISBLANK ( Measure_LM_Forecast )
                        && ISBLANK ( Measure_LM_Requested )
                        && ISBLANK ( Measure_2PM_Forecast )
                        && ISBLANK ( Measure_2PM_Requested )
                        && ISBLANK ( Measure_R3M_Forecast )
                        && ISBLANK ( Measure_R3M_Requested )
                        && ISBLANK ( Measure_R3M_Error )
                )
            RETURN
                -- Add the [R3M Measure] value to the table, but exclude the row if all measures are blank.
                FILTER ( ROW ( "@R3M Error", Measure_R3M_Error ), Include_Row )
        ),
        -- Use ALLSELECTED to ensure RankingTable is constructed in the overall filter context of the visual
        ALLSELECTED ()
    )
RETURN
    -- Finally, return the rank of [R3M Error] in current filter context, compared with all values in RankingTable
    RANKX ( RankingTable, [@R3M Error], [R3M Error], DESC, SKIP )

 

 

Simplified version:

 

Over rank = 
VAR RankingTable =
    CALCULATETABLE (
        GENERATE (
            CROSSJOIN (
                CALCULATETABLE (
                    SUMMARIZE (
                        'Final Table',
                        'Final Table'[Account Group],
                        'Final Table'[Salesperson],
                        'Final Table'[Region/Forecasting Entity],
                        'Final Table'[Warehouse],
                        'Final Table'[Product Name]
                    ),
                    REMOVEFILTERS ( 'Date Table' )
                ),
                SUMMARIZE (
                    'Date Table',
                    'Date Table'[Month Year],
                    'Date Table'[Year Month Number]
                )
            ),
            VAR Measure_R3M_Error = [R3M Error]
            VAR Include_Row =  NOT ISBLANK ( Measure_R3M_Error )
            RETURN
                FILTER ( ROW ( "@R3M Error", Measure_R3M_Error ), Include_Row )
        ),
        ALLSELECTED ()
    )
RETURN
    RANKX ( RankingTable, [@R3M Error], [R3M Error], DESC, SKIP )

 

Kind regards,

Owen 


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Hi Owen, that worked perfectly, appreciate the explanation as well. Thanks so much!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors