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
amirluna
Regular Visitor

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

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?

1 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)

2.PNG 

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

 

Regards,

Jimmy Tao

View solution in original post

3 REPLIES 3
amirluna
Regular Visitor

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?

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)

2.PNG 

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

 

Regards,

Jimmy Tao

Anonymous
Not applicable

Hi

 

Try this measure:

 

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

 

Thanks
Raj

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.