Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi!
Could you please help me to write a measure wich would calculate the most common sum of payments for each product.
For Product 1 I expest to see 300 and for Product 2 I expecet to see 500.
Thank you in advance for your help.
Solved! Go to Solution.
Hi @dmytro_po
Here is a one step solution. It combines the MEASURES above
Go to Modelling Tab>>> New Table
MyTable = SUMMARIZE ( FILTER ( ADDCOLUMNS ( ADDCOLUMNS ( SUMMARIZE ( TableName, TableName[Product], TableName[Customer], "MostCommonCost", SUM ( TableName[Payment] ) ), "CountPayments", VAR Currentsum = CALCULATE ( SUM ( TableName[Payment] ) ) VAR CurrentProduct = CALCULATE ( SELECTEDVALUE ( TableName[Product] ) ) RETURN COUNTROWS ( FILTER ( FILTER ( ALL ( TableName[Product], TableName[Customer] ), TableName[Product] = CurrentProduct ), CALCULATE ( SUM ( TableName[Payment] ) = Currentsum ) ) ) ), "RankPayments", RANKX ( ALL ( TableName[Product], TableName[Customer] ), CALCULATE ( [CountPayments] ), , DESC, DENSE ) ), [RankPayments] = 1 ), TableName[Product], [MostCommonCost] )
HI @dmytro_po
Try this solution
First Add a MEASURE that will count the Sum of Payments for each product
CountPayments = VAR Currentsum = SUM ( TableName[Payment] ) VAR CurrentProduct = SELECTEDVALUE ( TableName[Product] ) RETURN COUNTROWS ( FILTER ( FILTER ( ALL ( TableName[Product], TableName[Customer] ), TableName[Product] = CurrentProduct ), CALCULATE ( SUM ( TableName[Payment] ) = Currentsum ) ) )
You will get this
Now RANK the Count of Payments for each Product with this MEASURE
RankPayments = RANKX ( ALL ( TableName[Product], TableName[Customer] ), [CountPayments], , DESC, DENSE )
You will get
Hi @Zubair_Muhammad,
thanks for your help.
However, as the end result, I would like to receive the following table:
Meaning, without the need of showing customers and sum of payments.
Hi @dmytro_po
Here is a one step solution. It combines the MEASURES above
Go to Modelling Tab>>> New Table
MyTable = SUMMARIZE ( FILTER ( ADDCOLUMNS ( ADDCOLUMNS ( SUMMARIZE ( TableName, TableName[Product], TableName[Customer], "MostCommonCost", SUM ( TableName[Payment] ) ), "CountPayments", VAR Currentsum = CALCULATE ( SUM ( TableName[Payment] ) ) VAR CurrentProduct = CALCULATE ( SELECTEDVALUE ( TableName[Product] ) ) RETURN COUNTROWS ( FILTER ( FILTER ( ALL ( TableName[Product], TableName[Customer] ), TableName[Product] = CurrentProduct ), CALCULATE ( SUM ( TableName[Payment] ) = Currentsum ) ) ) ), "RankPayments", RANKX ( ALL ( TableName[Product], TableName[Customer] ), CALCULATE ( [CountPayments] ), , DESC, DENSE ) ), [RankPayments] = 1 ), TableName[Product], [MostCommonCost] )
Now you can add a visual level filter to get the RANK 1 i.e.most common sum of payments for each product.
User | Count |
---|---|
78 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
100 | |
91 | |
83 | |
61 |