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 column
This is the expected result:


Here is the PBIX Download .

 

wish that somebody can help, many thanks!

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

4 Replies

  • 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

  • BA_Pete's avatar
    BA_Pete
    Super User
    6 years ago

    Hi Anonymous ,

     

    Use this code to transform your data first:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSxkqxOhC+ERrfGInvBFdvBOcbofGNkfjOYPUgAhvXBawbrtgFrBmFa0Is1xXIMgVxTcFcNxjXCM41QeUaQrmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Sales = _t, Royality = _t]),
    chgSalesType = Table.TransformColumnTypes(Source,{{"Sales", Int64.Type}}),
    #"groupRows&Count" = Table.Group(chgSalesType, {"Customer", "Royality"}, {{"sales", each List.Sum([Sales]), type number}, {"visits", each Table.RowCount(_), Int64.Type}}),
    chgVisitsType = Table.TransformColumnTypes(#"groupRows&Count",{{"visits", type text}})
in
    chgVisitsType

    In Power Query, go to New Source > Blank Query then in Advanced Editor paste my code over the default code.

     

    Then in the report view, set up your matrix like this:

    Pete