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.

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Greg_Deckler : I adapted your formula with DISTINCTCOUNT, and it seemed to work.

Gregs_Measure_adapted = 
var _Sales = SUMMARIZE(Table1;Table1[Brand];"_Sales_";SUM(Table1[Sales]); "_Count_"; DISTINCTCOUNT(Table1[Articles])) 
Return
SUMX(FILTER(_Sales;[_Count_]>2);[_Sales_])

Do you think, it worked due to the special, simple design of the table?