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

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