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

DAX DistinctCount with SUMX totals

Hi, I know there are quite a few of these posts but I feel my case is a bit different since it has quite a few moving parts. I've tried various things and I'm still struggling to find a solution, hopefully someone can shed some light. 

So I'm trying to build a report on an existing dataset together with filters on that dataset in PowerBI.

The data would look something like this (I have simplified it to make it easier to work with).

 

HelpPic.png

I'm trying to create measures for the IsSold and IsReturned column according to the Customer_Id. I want to only count one sale or one return per customer_id regardless of how many sales or returns they had.

 

The first thing I tried was creating a measure which looked something like this:

Sales = CALCULATE(DISTINCTCOUNT(table[customer_id]), table[issold] = TRUE())

This counted the rows very well but the Total was never the SUM of the entire column (as expected).

 

I then tried;

SalesMeasure = SUMX(table, [Sales]) on the existing measure and this finally calculated the total well, but seemed to mess up the rows entirely, by ignoring the distinct counts.

 

I found another solution which used the VALUES function which looked something like:

SalesMeasure1 = SUMX(VALUES(table[Product_id], [Sales])), this worked well but the report is supposed to be quite dynamic, so when filtering and Product_id is no longer an existing column, it no longer worked as it should.

 

Basically trying to find a way that would provide the total distinct sales and returns per product_id (or whichever other column inserted), and the Total being the sum of the entire column.

 

Not sure If i made myself very clear, but if there are any questions please ask away.

 

Thanks in advance!

 

 

1 ACCEPTED SOLUTION

Hi @JM_nxgn ,

 

You can create measure like DAX below.

 

Sales = var d= CALCULATE(DISTINCTCOUNT(table[customer_id]), table[issold] = TRUE())
VAR _table = SUMMARIZE(table, table[Product_ID],"_Value", d)
RETURN
IF(HASONEVALUE(table[Product_ID]),d,SUMX(_table,[_Value]))

Best Regards,

Amy

 

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

4 REPLIES 4
CheenuSing
Community Champion
Community Champion

Hi @JM_nxgn ,

 

For the sample data pasted what is the output you expect.

 

Also if you can load the data to Google or One Drive and share the link it will help find a solution.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

The expected output would be what I have below. Unfortunately I can't provide the dataset as it contains sensitive information, I tried to recreate the situation as much as possible.

 

 

 Product_IDSales
   
 Prod - 11
 Prod - 22
 Prod - 31
 Prod - 41
 Prod - 51
 Prod - 63
TOTAL 9

Hi @JM_nxgn ,

 

You can create measure like DAX below.

 

Sales = var d= CALCULATE(DISTINCTCOUNT(table[customer_id]), table[issold] = TRUE())
VAR _table = SUMMARIZE(table, table[Product_ID],"_Value", d)
RETURN
IF(HASONEVALUE(table[Product_ID]),d,SUMX(_table,[_Value]))

Best Regards,

Amy

 

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

 

This worked for me thanks. 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors