Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |