Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Percent of Total based on Dynamic Filters

Hi All,

 

 I'm fairly new to the world of Power BI and just created some new dashboards. I have a quick question on how to display the % of total and this % changing based on the filter I select.

 

 For example, I want to calculate % of 22/35 and show this on my dashboard. So, I want the dashboard to show 63%; however, when I select a filter, the numbers change to 3/35. How can I set up a formula to show 63% and then as I click on filters, the % value to change accordingly?

 

 Any assistance would be greatly and sincerely appreciated. 

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

     

    According to your descritpion, you want to use filter to get the status and calculate the current status percent of total,right?

    If it is a case, you can refer to below sample:

     

    Table:(User,Status)

     

    Measure:

     

    SelectStatus = if(HASONEVALUE(Sheet1[Status]),VALUES(Sheet1[Status]),BLANK())

     

    Percent = DIVIDE(COUNTAX(FILTER(ALL(Sheet1),Sheet1[Status]=if(HASONEVALUE(Sheet1[Status]),VALUES(Sheet1[Status]),BLANK())),Sheet1[Status]),COUNTROWS(ALL(Sheet1)),0)

     

    Visuals:

    Card:

     

    Slicer:

     

     

    Result:

     

    Regards,

    Xiaoxin Sheng

     

4 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Icon for Super User rankSuper User
    9 years ago

    Hi Arslan,

     

    I have attached the screenshot for the reference. Create a calculated column for the percentage calculation and use Card Visual, This will filter your percentages automatically.

    create a calculated column with the formula shownCARD VISUAL

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thank for the reply, Bhavesh but I am still struggling.

       

      Here is my scenario, I need the count of "Yes" accounts (which equals to 22) and need this to be divided by the entire Account Pool (which is 35). 

       

      I am using this formula below but get the error message "The COUNT function only accpets a column reference as an argument".

       

       

      Percentage = COUNT('AIP Referenceability with or w/o contact'[AIP Reference Status]="Yes")/SUM('AIP Referenceability with or w/o contact'[Contact AIP Reference Status])

       

      I simply need to take the "Yes" Accounts (22) and divide by the entire Pool (35).

      • BhaveshPatel's avatar
        BhaveshPatel
        Icon for Super User rankSuper User
        9 years ago

        Create a conditional column in Query Editor which test the condition that if the account status is Yes, it will give you 1 or elso 0.

         

        and this will give you helper column.

         

        Use the formula I have shown to create a calculated column. 

         

        Percentage= Table1[Helper]/SUM(Table1[Helper]

         

        CONDITIONAL COLUMN IN QUERY EDITORCALCULATED COLUMN