DAX DistinctCount with SUMX totals

[JM_nxgn]

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

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!

[Anonymous]

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

[JM_nxgn]

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_ID	Sales
	Prod - 1	1
	Prod - 2	2
	Prod - 3	1
	Prod - 4	1
	Prod - 5	1
	Prod - 6	3
TOTAL		9

[v-xicai · 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.

[JM_nxgn]

This worked for me thanks.