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...
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.
Brand;Articles;Sales
A;Article1;4
A;Article2;2
A;Article1;2
A;Article1;5
B;Article3;10
B;Article3;105
B;Article3;26
C;Article1;24
C;Article2;27
C;Article3;85
C;Article4;63
D;Article5;15
D;Article5;45
E;Article5;65
E;Article2;35
E;Article2;85
E;Article2;91
E;Article1;10
Should be the result
- Ashish_Mathur
Super User
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.
Ashish_Mathur: That works perfectly! That means, there has to be an intermediate table to filter on, makes sense.
- Ashish_Mathur
You are welcome.