Forum Discussion

Roseventura's avatar
Roseventura
Icon for Responsive Resident rankResponsive Resident
6 years ago
Solved

DISTINCTCOUNT Total not correct

I know this issue has been raised ad nauseam, and I have tried many different approaches including ISFILTERED, HASONEFILTER, HASONEVALUE, and SUMMARIZE.  I still cannot get my table to give me a tota...
Need Help
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Roseventura 

How about: SUMX ( VALUES ( DimCalendar[Year-Mo] ), DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]) ) ?

 

(Replace name of your calendar-table)

 

Roseventura's avatar
Roseventura
Icon for Responsive Resident rankResponsive Resident
6 years ago

That gave me a different answer, but it's still not correct.

 

The measure I created using your suggestion, is:

CountCust Test = sumx( values(PBI_FSCAPF[YEAR-MO]), distinctcount(PBI_Disti_NICPoscy[ENDCUS] ))
 
where PBI_FSCAPF is my Calendar table.
 
 
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    In summarize try year month


    Total POS Customers =
    Var DISTINCTCUSTOMERS = summarize(PBI_Disti_NICPoscy,PBI_Disti_NICPoscy[Year-Mon],"DistCust",DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]) )
    RETURN
    SUMX(DISTINCTCUSTOMERS,[DistCust])

     

    Or share some sample data.

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
    In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
    Connect on Linkedin

    • Roseventura's avatar
      Roseventura
      Icon for Responsive Resident rankResponsive Resident
      6 years ago

      Your solution worked.  Here is my ORIGINAL measure which did not work:

       

      Total POS Customers =
      Var DISTINCTCUSTOMERS = summarize(PBI_Disti_NICPoscy,PBI_Disti_NICPoscy[ENDCUS],“DistCust”,DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]) )
      RETURN
      SUMX(DISTINCTCUSTOMERS,[DistCust])

       

      and here is your amended measure which did work:

       

      Total POS Custs =
      Var DISTINCTCUSTOMERS = summarize(PBI_Disti_NICPoscy,PBI_Disti_NICPoscy[Year-Mo],“DistCust”,DISTINCTCOUNT(PBI_Disti_NICPoscy[ENDCUS]) )
      RETURN
      SUMX(DISTINCTCUSTOMERS,[DistCust])

       

      The difference was the second argument in the SUMMARIZE table. I was using the ENDCUS field and I should have been using the YEAR-MO field.

       

      You can mark this solution as resolved.

       

      Thank you everyone!