Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Wrong total sum, distinct count

I have this table: I want to show only those brands with more than 2 distinct articles. This measure works fine for all the numbers in the table, but total sum fails. I was not able to...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    I believe you want something like this, should have the right total:

     

    Measure 2 = 
VAR __Sales = 
    SUMMARIZE(articles,[Brand],"__Sales",SUM([Sales]),"__Count",COUNTX(DISTINCT(SUMMARIZE(articles,[Articles])),[Articles]))
RETURN
    SUMX(FILTER(__Sales,[__Count]>2),[__Sales])

     

    See attached, Page 2, articles table.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Try these measures

    Total sales = SUM(Data[Sales])
    Number of articles = DISTINCTCOUNT(Data[Articles])
    Measure = SUMX(FILTER(SUMMARIZE(VALUES(Data[Brand]),Data[Brand],"ABCD",[Number of articles],"EFGH",[Total sales]),[ABCD]>2),[EFGH])

    Hope this helps.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous try this measure

 

Sum of distinct count = 
SUMX ( VALUES ( Table[Brand] ), [Z_DistCount] )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you, parry2k, but gives me not the correct result.

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      I believe you want something like this, should have the right total:

       

      Measure 2 = 
VAR __Sales = 
    SUMMARIZE(articles,[Brand],"__Sales",SUM([Sales]),"__Count",COUNTX(DISTINCT(SUMMARIZE(articles,[Articles])),[Articles]))
RETURN
    SUMX(FILTER(__Sales,[__Count]>2),[__Sales])

       

      See attached, Page 2, articles table.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Greg_Deckler: This is very neat, too. Thank you, Greg. So the part where you state

         

        COUNTX(DISTINCT(SUMMARIZE(

         

        caters for older version of Power BI, am I right? The younger formula

         

        DISTINCTCOUNT()

         

        makes it shorter than before, correct?