cancel
Showing results for
Did you mean:
Helper III

## 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:

wish that somebody can help, many thanks!

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

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

Super User

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

Super User

@SimonChung_GGGG , see if segmentation can help for frequency

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

#### Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!