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, 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!

 

 

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

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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

      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

        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.