Forum Discussion

derekli17001's avatar
derekli17001
Icon for Helper I rankHelper I
1 year ago
Solved

My Matrix Table Keeps Showing More than X rows despite Measures - How to fix?

Hi - Currently my table (named Overview) is displaying Sites (via distinct site table) and Customers (via distinct customer table) as the Matrix Table's rows and the overall store sales + top 5 custo...
Need Help
Tips and Tricks
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    HI, 

    I am not sure if I understood your question correctly, but in my opinion, only adding top 5 of each store is correctly shown. However, if you still want to show all total, then please try something like below.

    Customer top5 by store =
VAR BaseTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE ( Overview, 'Unique Customers'[Customer], 'Unique Stores'[Store] ),
            REMOVEFILTERS ( 'Unique Customers'[Customer] ),
            ALLSELECTED ( 'Date'[Year] )
        ),
        "@val", CALCULATE ( [Total Sales], ALLSELECTED ( 'Date'[Year] ) )
    )
VAR Result =
    CALCULATE (
        [Total Sales],
        KEEPFILTERS (
            WINDOW (
                1,
                ABS,
                5,
                ABS,
                BaseTable,
                ORDERBY ( [@val], DESC ),
                ,
                PARTITIONBY ( 'Unique Stores'[Store] )
            )
        )
    )
RETURN
    IF ( NOT ISINSCOPE ( 'Unique Customers'[Customer] ), [Total Sales], Result )



     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
1 year ago

Hi,

Please try the below and the attached pbix file whether it works.

- remove condition (customer is visible) from filter pane

- add the below measure to the visual.

 

Customer top5 by store =
VAR BaseTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE ( Overview, 'Unique Customers'[Customer], 'Unique Stores'[Store] ),
            REMOVEFILTERS ( 'Unique Customers'[Customer] ),
            ALLSELECTED ( 'Date'[Year] )
        ),
        "@val", CALCULATE ( [Total Sales], ALLSELECTED ( 'Date'[Year] ) )
    )
VAR Result =
    CALCULATE (
        [Total Sales],
        KEEPFILTERS (
            WINDOW (
                1,
                ABS,
                5,
                ABS,
                BaseTable,
                ORDERBY ( [@val], DESC ),
                ,
                PARTITIONBY ( 'Unique Stores'[Store] )
            )
        )
    )
RETURN
    Result

 

 

derekli17001's avatar
derekli17001
Icon for Helper I rankHelper I
1 year ago

Hi - thanks for that, it appears that the store subtotal and overall values seem to be inaccurate for your file

as the total should be 381M (based off totalling the data in the excel sheet) - is there a way to fix this? Thanks.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    1 year ago

    HI, 

    I am not sure if I understood your question correctly, but in my opinion, only adding top 5 of each store is correctly shown. However, if you still want to show all total, then please try something like below.

    Customer top5 by store =
VAR BaseTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE ( Overview, 'Unique Customers'[Customer], 'Unique Stores'[Store] ),
            REMOVEFILTERS ( 'Unique Customers'[Customer] ),
            ALLSELECTED ( 'Date'[Year] )
        ),
        "@val", CALCULATE ( [Total Sales], ALLSELECTED ( 'Date'[Year] ) )
    )
VAR Result =
    CALCULATE (
        [Total Sales],
        KEEPFILTERS (
            WINDOW (
                1,
                ABS,
                5,
                ABS,
                BaseTable,
                ORDERBY ( [@val], DESC ),
                ,
                PARTITIONBY ( 'Unique Stores'[Store] )
            )
        )
    )
RETURN
    IF ( NOT ISINSCOPE ( 'Unique Customers'[Customer] ), [Total Sales], Result )