Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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,
@v-jiascu-msftThanks, these work as a solution to the problem I was having and well for that matter, the problem is that these measures take ages to calculate for my data which is alot of transactions spread over the last 6 months hence slow load times for my matrix table visual. Is there a better approach to reduce the perfomance hit since it seems it is iterating alot?
Hi @Kevin_Gitonga ,
I think the performance of @Ashish_Mathur 's solution is better. Maybe you can join two solutions together.
Best Regards,
Hi,
If in the Grand total column of Total billing, you want to see the summation of the numbers in the Billing column, that should be easy to do. However, i think that result would be wrong. The figues as they appear now seem correct to me. This is simply because the same Customer may have bought multiple products in the same invoice. So the Grand Total column for Billing should not be a straight summation.
Regarding the Golden Point columns:
For your final "Golden Point" column on the right, I have a suspicion it's related to the "Total Billing" column, so that if the value in the "Total Billing" column is >=1, then the "Golden Point" column value will be 1, based on this snippet you posted below:
Golden Point = if([Billing]>=1,1,0)
It sounds like you want that to be "Total Golden Points" instead. If that's correct then you'll want to re-write the code for that column so that it checks each of those 4 "Golden Point" columns and adds +1 to the "Total Golden Points" column when a golden point has been awarded for client/product match up.
Regarding the "Total Billing" column:
Do you have other code or calculations specific to that you could share with us? I eyeballed your data but couldn't figure out how the numbers in the "Total Billing" column were being calculated.
Yes I want the Total Golden Points, how do I go about wrirng a formula that would add the values per column to find the total golden points??. The logic behind billing is finding the number of disctint Invoices for a customer that contain a partilucar product used as the column in the matrix table. Currently the total billing is also not giving the correct sum of the values in the columns after using distinct count.Here is the sample data. I'd like the row totals to give the correct totals for the row
TxDate | CustomerCode | InvNumber | ProductCode | Qty |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC800 | 4 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC302 | 5 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC750 | 5 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC451 | 5 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC400 | 5 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC303 | 2 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC650 | 2 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC401 | 4 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC751 | 2 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC152 | 2 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC301 | 24 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC500 | 24 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC450 | 24 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC552 | 3 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC551 | 2 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | ME101 | 54 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | ME100 | 5 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MB103 | 0 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MB101 | 40 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC700 | 2 |
3/1/2019 | TUS56-NBI | NBI_INV227866 | MC150 | 24 |
3/1/2019 | QUI03-NBI | NBI_INV227845 | ME101 | 162 |
3/1/2019 | QUI03-NBI | NBI_INV227845 | MD203 | 3 |
3/1/2019 | QUI03-NBI | NBI_INV227845 | MD202 | 4 |
3/1/2019 | QUI03-NBI | NBI_INV227845 | MD201 | 4 |
3/1/2019 | QUI03-NBI | NBI_INV227845 | MD200 | 1 |
3/1/2019 | QUI03-NBI | NBI_INV227845 | MC550 | 2 |
3/1/2019 | QUI03-NBI | NBI_INV227845 | ME100 | 3 |
3/1/2019 | QUI03-NBI | NBI_INV227845 | MC150 | 1 |
3/1/2019 | QUI03-NBI | NBI_INV227845 | MC100 | 1 |
3/1/2019 | QUI03-NBI | NBI_INV227845 | MC600 | 15 |
3/1/2019 | QUI03-NBI | NBI_INV227845 | FN002 | 0 |
3/1/2019 | FAS01-NBI | NBI_INV227846 | FK020 | 6 |
3/1/2019 | FAS01-NBI | NBI_INV227846 | FK021 | 6 |
3/1/2019 | FAS01-NBI | NBI_INV227846 | FT005 | 5 |
3/1/2019 | FAS01-NBI | NBI_INV227846 | UB011 | 20 |
3/1/2019 | FAS01-NBI | NBI_INV227846 | UB032 | 6 |
3/1/2019 | FAS01-NBI | NBI_INV227846 | UB382 | 1 |
3/1/2019 | FAS01-NBI | NBI_INV227846 | UB383 | 1 |
3/1/2019 | WA04-NBI | NBI_INV227847 | FT005 | 5 |
3/1/2019 | WA04-NBI | NBI_INV227847 | EVE116 | 1 |
3/1/2019 | WA04-NBI | NBI_INV227847 | EVE113 | 1 |
3/1/2019 | QUI03-NBI | NBI_INV227850 | FT005 | 5 |
3/1/2019 | QUI03-NBI | NBI_INV227850 | FT001 | 0 |
3/1/2019 | QUI03-NBI | NBI_INV227850 | KEL141 | 12 |
3/1/2019 | QUI03-NBI | NBI_INV227850 | KEL121 | 16 |
3/1/2019 | QUI03-NBI | NBI_INV227850 | KEL140 | 16 |
3/1/2019 | QUI03-NBI | NBI_INV227850 | KEL120 | 10 |
3/1/2019 | QUI03-NBI | NBI_INV227850 | UB010 | 24 |
3/1/2019 | QUI03-NBI | NBI_INV227850 | MB103 | 0 |
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.
@Ashish_MathurThanks, this actually works well for what I wanted to achieve in the billing measure. Let me try and understand the logic of this formula better. I'm only having one problem whereby the Grand total for the Golden points is not being computed for the rows, can I use the same logic used in this formula?
You are welcome. For Golden points, use the same logic.
@Ashish_MathurI tried the Golden point using the following formula and i'm gettting the correct total for the rows, but the total for the columns at the bottom is not correct, what could be the issue?
Golden Point = if(HASONEVALUE(Sales[ProductCode]),if(DISTINCTCOUNT(Sales[InvNumber])>=1,1,0),SUMX(SUMMARIZE(VALUES(Sales[ProductCode]),[ProductCode],"ABCD",if(DISTINCTCOUNT(Sales[InvNumber])>=1,1,0)),[ABCD]))
Hi,
Try this simple measure for Golden Points
Golden Points = 1*[Billing]
Hi,
Share the link from where i can download your PBI file.
@Ashish_MathurHere's the link to the PBI file I'm using
https://www.dropbox.com/s/m9rlnrqu9afrial/Sales%20dashboard%20test-%20Copy.pbix?dl=0
hi @Ashish_Mathur were you able to have a glance of the linked pbix file I'm using
Hi,
Try this measure for Golden Points
Golden Point = if(HASONEVALUE(Sales[CustomerCode]),if(HASONEVALUE(Sales[ProductCode]),if(DISTINCTCOUNT(Sales[InvNumber])>=1,1,0),SUMX(SUMMARIZE(VALUES(Sales[ProductCode]),[ProductCode],"ABCD",if(DISTINCTCOUNT(Sales[InvNumber])>=1,1,0)),[ABCD])),SUMX(SUMMARIZE(VALUES(Sales[CustomerCode]),[CustomerCode],"ABCD",if(DISTINCTCOUNT(Sales[InvNumber])>=1,1,0)),[ABCD]))
Hope this helps.
Hi, @Ashish_Mathur, Thanks for your assistance so far. I tried this and it works well for the row total, however the total of the row total column(Bottom left) is not givign the right answer e.g here it gives 320 while in the real sense it should be 657 when you compute the totals.
Hi,
I'll need to see the PBI file.
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.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |