Forum Discussion
Wrong total sum, distinct count
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.
Community ChampionI 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: 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.
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?