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

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

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

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.