Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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
A million thanks! This question has been struggling me for a few days
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
Proud to be a Datanaut!
@Anonymous , 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-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |