Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
Solved! Go to 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
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:
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)
PBIX for your reference: https://www.dropbox.com/s/oc4mnp2bo1lpd8d/Count%20the%20numer%20of%20rows%20with%20specific%20value%....
Regards,
Jimmy Tao
Hi
Try this measure:
actual_cnt = SUMX(SUMMARIZE(Table1, Table1[Group],"successrate", MAX(Table1[SuccessGroup])),[successrate])
Thanks
Raj
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |