cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kevin_Gitonga
Helper I
Helper I

Aggregating Distinct Count measure and Aggregating IF measure

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

Matrix table.JPG

 

Kindly help me on how I can make the row totals to be accurate.

3 ACCEPTED SOLUTIONS
v-jiascu-msft
Microsoft
Microsoft

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

23 REPLIES 23
v-jiascu-msft
Microsoft
Microsoft

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User III
Super User III

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

TxDateCustomerCodeInvNumberProductCodeQty
3/1/2019TUS56-NBINBI_INV227866MC8004
3/1/2019TUS56-NBINBI_INV227866MC3025
3/1/2019TUS56-NBINBI_INV227866MC7505
3/1/2019TUS56-NBINBI_INV227866MC4515
3/1/2019TUS56-NBINBI_INV227866MC4005
3/1/2019TUS56-NBINBI_INV227866MC3032
3/1/2019TUS56-NBINBI_INV227866MC6502
3/1/2019TUS56-NBINBI_INV227866MC4014
3/1/2019TUS56-NBINBI_INV227866MC7512
3/1/2019TUS56-NBINBI_INV227866MC1522
3/1/2019TUS56-NBINBI_INV227866MC30124
3/1/2019TUS56-NBINBI_INV227866MC50024
3/1/2019TUS56-NBINBI_INV227866MC45024
3/1/2019TUS56-NBINBI_INV227866MC5523
3/1/2019TUS56-NBINBI_INV227866MC5512
3/1/2019TUS56-NBINBI_INV227866ME10154
3/1/2019TUS56-NBINBI_INV227866ME1005
3/1/2019TUS56-NBINBI_INV227866MB1030
3/1/2019TUS56-NBINBI_INV227866MB10140
3/1/2019TUS56-NBINBI_INV227866MC7002
3/1/2019TUS56-NBINBI_INV227866MC15024
3/1/2019QUI03-NBINBI_INV227845ME101162
3/1/2019QUI03-NBINBI_INV227845MD2033
3/1/2019QUI03-NBINBI_INV227845MD2024
3/1/2019QUI03-NBINBI_INV227845MD2014
3/1/2019QUI03-NBINBI_INV227845MD2001
3/1/2019QUI03-NBINBI_INV227845MC5502
3/1/2019QUI03-NBINBI_INV227845ME1003
3/1/2019QUI03-NBINBI_INV227845MC1501
3/1/2019QUI03-NBINBI_INV227845MC1001
3/1/2019QUI03-NBINBI_INV227845MC60015
3/1/2019QUI03-NBINBI_INV227845FN0020
3/1/2019FAS01-NBINBI_INV227846FK0206
3/1/2019FAS01-NBINBI_INV227846FK0216
3/1/2019FAS01-NBINBI_INV227846FT0055
3/1/2019FAS01-NBINBI_INV227846UB01120
3/1/2019FAS01-NBINBI_INV227846UB0326
3/1/2019FAS01-NBINBI_INV227846UB3821
3/1/2019FAS01-NBINBI_INV227846UB3831
3/1/2019WA04-NBINBI_INV227847FT0055
3/1/2019WA04-NBINBI_INV227847EVE1161
3/1/2019WA04-NBINBI_INV227847EVE1131
3/1/2019QUI03-NBINBI_INV227850FT0055
3/1/2019QUI03-NBINBI_INV227850FT0010
3/1/2019QUI03-NBINBI_INV227850KEL14112
3/1/2019QUI03-NBINBI_INV227850KEL12116
3/1/2019QUI03-NBINBI_INV227850KEL14016
3/1/2019QUI03-NBINBI_INV227850KEL12010
3/1/2019QUI03-NBINBI_INV227850UB01024
3/1/2019QUI03-NBINBI_INV227850MB1030

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

@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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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]))


2 Matrix table.JPG

 

Hi,

 

Try this simple measure for Golden Points

 

Golden Points = 1*[Billing]

 
Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.GP.JPG

 

Hi,

I'll need to see the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Thanks, this worked well

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.