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.
I have the following matrix table with the columns being products and the rows being the customer codes. What I want is to find the billings which is the distinct count of orders.I used the following for billing
Billing = DISTINCTCOUNT(Sales[InvNumber])
From that I am supposed to find golden points which is where if the billing(distinct count of orders) is more than 1 then Golden is one otherwise the golden point is 0.I used the following
Golden Point = if([Billing]>=1,1,0)
The problem is both these two measures do not show the row totals accurately as shown below.Total billing is not adding up and the Golden points is showing 1 for all in the totals column
Kindly help me on how I can make the row totals to be accurate.
Solved! Go to Solution.
Hi @Kevin_Gitonga,
Try these two measures, please.
Measure = SUMX ( SUMMARIZE ( 'table', 'table'[ProductCode], 'table'[CustomerCode], "Billing", DISTINCTCOUNT ( 'table'[InvNumber] ) ), [Billing] )
Total Golden Point = SUMX ( SUMMARIZE ( 'table', 'table'[ProductCode], 'table'[CustomerCode], "Golden Point", IF ( DISTINCTCOUNT ( 'table'[InvNumber] ) >= 1, 1, 0 ) ), [Billing] )
Best Regards,
Hi,
Try this measure
Billing = if(HASONEVALUE(Data[ProductCode]),DISTINCTCOUNT(Data[InvNumber]),SUMX(SUMMARIZE(VALUES(Data[ProductCode]),[ProductCode],"ABCD",DISTINCTCOUNT(Data[InvNumber])),[ABCD]))
Hope this helps.
Hi,
The Golden Points measure can be revised to:
Golden Point = SUMX(SUMMARIZE(GENERATE(VALUES(Sales[CustomerCode]),VALUES(Sales[ProductCode])),[CustomerCode],[ProductCode],"ABCD",if(DISTINCTCOUNT(Sales[InvNumber])>=1,1,0)),[ABCD])
Hope this helps.
You are welcome.
@Ashish_MathurI have a query on this, am I able to filter the products used in this formula using the values in another table.
Golden Point = SUMX(SUMMARIZE(GENERATE(VALUES(Sales[CustomerCode]),VALUES(Sales[ProductCode])),[CustomerCode],[ProductCode],"ABCD",if(DISTINCTCOUNT(Sales[InvNumber])>=1,1,0)),[ABCD])
The products to filter in the formula are based on this table where different periods have diffrent products used in the Golden point formula above.
Hi,
I am not clear with your question. Please share a simple dataset, explain the business context and show the expected result.
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |