Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ybyb23
Frequent Visitor

counting true/ false in table and return % of true

Hi all, 

 

I have created a key measure condition to return true or false from my viz table.

Here is an example below, I have multiple rows, with the condition true or false and I am trying to calculate based on this condition what's the % of true by counting the rows, however I am facing some difficulties with the simple dax countrows. 

Would be so nice to have your help on that, thank you!! 

 

Product IDProduct NameMarketapplicationsales CYsales PYCondi
1452454tomatoesstoretomato sauce 12451235True
1452454tomatoestruck foodpizza4578 False
1 ACCEPTED SOLUTION
ybyb23
Frequent Visitor

So I was able to find a solution for this issue, it might be helpful for some other BI users

 

I have used first 

var _count = SUMX(
    VALUES(Table[Product ID]),
     
    MAXX(SUMMARIZE(Table,Table[Market], Table[Product],Table[productID]),
    CALCULATE(DISTINCTCOUNT(Table[ProductID]))))
 
then based on the condition : 
var _count1 = SUMX(
    VALUES(Table[productID]),
    if ([var _count2] = TRUE,
MAXX(SUMMARIZE(Table,Table[Market], Table[Product],Table[productID]),
    CALCULATE(DISTINCTCOUNT(Table[ProductID])))) 
 
then I divide the second on the first 😄 

View solution in original post

3 REPLIES 3
PurpleGate
Resolver III
Resolver III

Instead of summing up the True/False measure, you could use that calculation in the sum.

 

Using your data, I made a simple [True/False] measure

Measure Condi = IF(SELECTEDVALUE('Table'[Market]) = "store", "True", "False")
If the market is "store" then its true
 
Then to count the rows that had this, instead of summing up the measure, I made a new measure where I said count the rows where market is "store" (instead of count the rows that are "true")
count True rows = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Market]= "store"))
 
 
And then to get the percentage, I made a new measure saying divide the rows that are "store" with all the rows (instead of divide "true" rows with all rows)
Percent true rows = DIVIDE(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Market] = "store")),COUNTROWS('Table'))
 
PurpleGate_0-1663849639881.png

 

 
 
 
 
 

Hi, thanks a lot for the prompt reply. That's a good way to do it, however the way how my data are this will not be possible because  the condition is the one that could defferienciate between the product IDs if it is true or false, also it has to be dynamic otherwise drilling down and up I cannot get the % 

 

I I have tried it this way, but I am stuck with counting the rows

 

 all the unique rows 
var _count = MAXX(
SUMMARIZE('Table', 'Table'[product],'TABLE'[Market ],'table'[ProductID]),
DISTINCTCOUNT(J11[ProductID]) )
 
then this to get out where I have only true
var _count1 = IF ( [var _count2] = true,
MAXX(
SUMMARIZE('Table', 'Table'[product],'Table'[Market ],'Table'[ProductID]),
DISTINCTCOUNT(Table[ProductID]) ), BLANK())
 
However if I would like to sum and get the % it's not working....

 

ybyb23
Frequent Visitor

So I was able to find a solution for this issue, it might be helpful for some other BI users

 

I have used first 

var _count = SUMX(
    VALUES(Table[Product ID]),
     
    MAXX(SUMMARIZE(Table,Table[Market], Table[Product],Table[productID]),
    CALCULATE(DISTINCTCOUNT(Table[ProductID]))))
 
then based on the condition : 
var _count1 = SUMX(
    VALUES(Table[productID]),
    if ([var _count2] = TRUE,
MAXX(SUMMARIZE(Table,Table[Market], Table[Product],Table[productID]),
    CALCULATE(DISTINCTCOUNT(Table[ProductID])))) 
 
then I divide the second on the first 😄 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors