count the numer of rows within a visualization table that has a "1" in a measure

[amirluna]

Hi everybody.

Im trying to count the numer of rows within a visualization table that has a "1" in a measure.

My source table is like

SalesMan Group Target    Sales

John            A          75      50

Marc           A           50      25            

Peter           A           60       50

John            B           30      35

Marc           B           50      55            

Peter           B           60       65

John            C           25      30

Marc           C           40      20            

Peter           C           35      50

For group C all the salesman got the target.

The measure formula is:

Spoiler

SuccessGroup = IF([Sales]/[Terget]>=1,VALUE(INT(1)),VALUE(INT(0)))

Then in my visualization table in Power BI I get:

Group   SucessGroup

A              0

B              1

C              1

I want to get the value "2" in a measue because only two groups got the target.

But if i count the numer of rows I get the overall value of rows ("6") from the source table for each salesman where them got the target.

¿What formula can i do to get "2" as measure value?

[amirluna]

Hi everybody.

Im trying to count the numer of rows within a visualization table that has a "1" in a measure.

My source table is like

SalesMan Group Target    Sales

John            A          75      50

Marc           A           50      25            

Peter           A           60       50

John            B           30      35

Marc           B           50      55            

Peter           B           60       65

John            C           25      30

Marc           C           40      20            

Peter           C           35      50

For group C all the salesman got the target.

The measure formula is:

Spoiler

SuccessGroup = IF([Sales]/[Terget]>=1,VALUE(INT(1)),VALUE(INT(0)))

Then in my visualization table in Power BI I get:

Group   SucessGroup

A              0

B              1

C              1

I want to get the value "2" in a measue because only two groups got the target.

But if i count the numer of rows I get the overall value of rows ("3") from the source table for each salesman where them got the target.

¿What formula can i do to get "2" as value?

[v-yuta-msft · Accepted solution]

Hi amirluna,

To achieve your requirement, you can create a calculate column instead of measure using DAX below:

SuccessGroup = IF([Sales] / [Target] >= 1, 1/3, 0)

Then create a measure to achieve count number:

Count = ROUNDUP(SUM(Table1[SuccessGroup]), 0)

 

PBIX for your reference: https://www.dropbox.com/s/oc4mnp2bo1lpd8d/Count%20the%20numer%20of%20rows%20with%20specific%20value%....

Regards,

Jimmy Tao

[Anonymous]

Hi

Try this measure:

actual_cnt = SUMX(SUMMARIZE(Table1, Table1[Group],"successrate", MAX(Table1[SuccessGroup])),[successrate])

Thanks
Raj