cancel
Showing results for
Did you mean:
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

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

3 ACCEPTED SOLUTIONS
Microsoft

```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.
Super User III

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
Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
23 REPLIES 23
Microsoft

```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.
Helper I

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

Microsoft

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

Helper I

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
Super User III

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
Helper I

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

Super User III

You are welcome.  For Golden points, use the same logic.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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

Super User III

Hi,

Try this simple measure for Golden Points

Golden Points = 1*[Billing]

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User III

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I
Helper I

hi @Ashish_Mathur were you able to have a glance of the linked pbix file I'm using

Super User III

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
Helper I

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.

Super User III

Hi,

I'll need to see the PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I
Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I
Thanks, this worked well

Announcements