Forum Discussion

JM_nxgn's avatar
JM_nxgn
Icon for Helper I rankHelper I
7 years ago
Solved

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, hop...
Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    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's avatar
JM_nxgn
Icon for Helper I rankHelper I
7 years ago

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
v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    JM_nxgn
    Icon for Helper I rankHelper I
    7 years ago

    This worked for me thanks.