Count all "1" in a column created using the measure

[Anonymous]

Hi Community,

I am trying to sum all the "1" in a measure, which should then be visualized in a tile. If I set the column into my datatable it shows the "1" in the right columns, however the total in the bottum is 0. I think my measure somehow is turning it into a true/false statement, which might could be the problem. I have added my measure and a table below in order to indicate what I am trying to achieve.

Styles Not Bought = 

if(HASONEFILTER(Style_Status[Style_Key]);

CALCULATE(

 DISTINCTCOUNT(Fact_Open_Sales_Colour[Style Key]);

 FILTER(Style_Status;[Incoming Order2]=0);

 Filter(Fact_Open_Sales_Colour;[Sold EUR C]>=0);

 FILTER(Fact_Open_Sales_Colour;DISTINCTCOUNT(Fact_Open_Sales_Colour[Holding Name])=

 DISTINCTCOUNT(Dim_Customers_Detail[HOLDING_NAME])));0)

Stylekey	Holding Name	Sold	Styles Not Bought
123456789	Company A	100	1
234567891	Company B	90	0
345678912	Company C	80	0
456789123	Company D	70	1
567891234	Company E	60	0
Total			0

The Tile/Measure should then count the two "1" and show a total of 2.

[v-gizhi-msft]

Hi,

According to your description, i create a table to test:

Then, create a measure:

Measure = SUMX(DISTINCT('Table'[ID]),IF(CALCULATE(SUM('Table'[Sales]))>110,1,0))

And it shows the correct total:

Hope this helps.

Best Regards,

Giotto Zhi

[Xriz]

Hello @Anonymous,

did you try to wrap the whole thing into a sum? The outer function is an IF-statement.. so PBI doesn't know how to aggregate.

Cheers Xriz

[Anonymous]

Just to be sure that I understand what you are writing.

You want me to put "Sum()" around the whole thing? If that is the case, then it gives me an error saying the 'sum function only acceots a column reference as an argument'.

[Xriz · Accepted solution]

Create a new measure with sumx(Facttable;[Measure with IF-Statement]). This measure should distinguish each row from 0 and 1 and aggregate correctly in the total.

Does it work?

Cheers