cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jonasheise Regular Visitor
Regular Visitor

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

Accepted Solutions
Xriz Frequent Visitor
Frequent Visitor

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

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
Xriz Frequent Visitor
Frequent Visitor

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

Hello @jonasheise,

 

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

jonasheise Regular Visitor
Regular Visitor

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

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 Frequent Visitor
Frequent Visitor

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

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

Microsoft v-gizhi-msft
Microsoft

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

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

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors