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

You are welcome.

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

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

Super User III

Hi,

I am not clear with your question.  Please share a simple dataset, explain the business context and show the expected result.

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

Announcements

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

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors