Forum Discussion

jalaomar's avatar
jalaomar
Icon for Helper IV rankHelper IV
5 years ago

Count rows where If statements are true

Hi all,

 

I need to sum up the total of all true statements = 1 for column VSU 5 weeks and in the screenshot you can see that my attempt is not working. How could I fix this?

 

(I don't have 1350 rows in my table as I have filtered out many projects)

 

the column VSU 5 weeks is a true, false statement so it return a string.

 

I would like to get the Total sum for the column with rows = 1

and then I would like to divide with the total number of rows in the table to get the %

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    jalaomar VSU 5 Weeks is a measure so that won't work the way you are doing it. Essentially what you have is a measures total problem. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

    Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

    • jalaomar's avatar
      jalaomar
      Icon for Helper IV rankHelper IV
      5 years ago

      Hi Greg_Deckler I tried to create a measure but still I am not sure if I have understood how to create this measure fully since it is not working.

       

      the value 1 is a string in the measure VSU 5 Weeks and hence not able to use the SUMX.

      any suggestions on how to create this measure?

       

      My measures are the following in the table 

       

      VSU Days = DATEDIFF('KPI'[MIS3.1Actual 2];'KPI'[ITG4Actual2];DAY)
      VSU 5 weeks2 = IF(ISBLANK('KPI'[VSU Days]) && 'KPI'[VSU Days]<=35; "0";"1")

       

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        5 years ago

        jalaomar You need to SUMX across a SUMMARIZE or SUMMARIZE columns of you data which mimics how the data in the visual is being summarized.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi jalaomar 

    Because you've filtered out many projects, you need perform the same filtering operation in your measure when you count the rows.

    Could you share a sample? so that we can work on it further.

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.