Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Frequency Matrix Table

Hi PB Experts,   I've tried hard to create a table but fail.   The data are simplified as this   I would like to make a matrix table, no. of visit (no. of Sales) will be displayed in colu...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    For your case, just create a measure with logic as below:

    Result = 
VAR _table =
    FILTER (
        CROSSJOIN (
            SUMMARIZE (
                'Table',
                'Table'[Royality],
                'Table'[Customer],
                "Totalsales", CALCULATE ( SUM ( 'Table'[Sales] ) ),
                "Frequency", CALCULATE ( COUNTA ( 'Table'[Customer] ) )
            ),
            Visit
        ),
        [Frequency] = [Value]
    )
RETURN
    SUMX ( _table, [Totalsales] )

    Result:

     

    Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi  Anonymous 

For your case, just create a measure with logic as below:

Result = 
VAR _table =
    FILTER (
        CROSSJOIN (
            SUMMARIZE (
                'Table',
                'Table'[Royality],
                'Table'[Customer],
                "Totalsales", CALCULATE ( SUM ( 'Table'[Sales] ) ),
                "Frequency", CALCULATE ( COUNTA ( 'Table'[Customer] ) )
            ),
            Visit
        ),
        [Frequency] = [Value]
    )
RETURN
    SUMX ( _table, [Totalsales] )

Result:

 

Regards,

Lin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    A million thanks! This question has been struggling me for a few days