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?

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

        Unless it has changed recently, DISTINCTCOUNT only accepts an actual column in an actual table as a parameter. Thus, if we are working with a table expression, we cannot use DISTINCTCOUNT and have to essentially code around it in the manner shown. There is no DISTINCTCOUNTX function like there is COUNTX so we have to work around that.