Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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)
 
StylekeyHolding NameSoldStyles Not Bought
123456789Company A1001
234567891Company B900
345678912Company C800
456789123Company D701
567891234Company E600
Total  0

 

 

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

1 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

View solution in original post

4 REPLIES 4
v-gizhi-msft
Community Support
Community Support

Hi,

 

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

31.PNG

Then, create a measure:

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

And it shows the correct total:

32.PNG

 

Hope this helps.

 

Best Regards,

Giotto Zhi

 

Xriz
Advocate I
Advocate I

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
Not applicable

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'.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.