Forum Discussion
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 adapt for Greg's solution.
Is there a DAX formula to do so?
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.
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.
12 Replies
Anonymous try this measure
Sum of distinct count = SUMX ( VALUES ( Table[Brand] ), [Z_DistCount] )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.
- Can you post that raw data as text and can you also post your expected result of your sample data?
