Wrong total sum, distinct count

[Anonymous]

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?

[Greg_Deckler · Accepted solution]

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.

[Greg_Deckler]

Can you post that raw data as text and can you also post your expected result of your sample data?

[Anonymous]

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

[Anonymous]

Should be the result

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

@Ashish_Mathur: That works perfectly! That means, there has to be an intermediate table to filter on, makes sense.

[Ashish_Mathur]

You are welcome.

[parry2k]

@Anonymous try this measure

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

[Anonymous]

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

[Greg_Deckler · Accepted solution]

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]

@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]

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]

@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?