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.

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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