Frequency Matrix Table

[SimonChung_GGGG]

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!

[v-lili6-msft · Accepted solution]

hi  @SimonChung_GGGG 

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

[SimonChung_GGGG]

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

[BA_Pete]

Hi @SimonChung_GGGG ,

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

[amitchandak]

@SimonChung_GGGG , see if segmentation can help for frequency

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization