Forum Discussion

John2's avatar
John2
Frequent Visitor
2 years ago
Solved

DAX formula help needed

I have a table called patch which has computer id, patch name, compliant count with values 0,1. The table has details of various ids, the patches associated with each of them and if they are compliant or not(if they have been installed or not). 
 
I have then made a dax called compliant % with formula as below:
Compliant_ % = 
var compliant_count = sum(patch[Compliant Count])
var applicable_system_count = COUNTROWS(patch)
return
DIVIDE(compliant_count,applicable_system_count,0).
 
I have made a graph which shows every computer id and their respective compliant %.
My next task it to make a compliant% range values say, 10-25,25-30% etc and show how many ids are in those ranges. How can I do that?
 
I have tried making a dax but since the ranges are text values I am not able to make graphs. I then tried used values like 1,2,3,4 to represent ranges & got the result I needed.
 
Compliance_catergory =
IF([Compliant %] >= 0.10 && [Compliant %] <= 0.18, "10-18%",
IF([Compliant %] >= 0.045 && [Compliant %] < 0.10, "4.5-10%",
IF([Compliant %] >= 0.02 && [Compliant %] < 0.045, "2-4.5%",
IF([Compliant %] >= 0.01 && [Compliant %] < 0.02, "1-2%",
"others"
)
)
)
)
But I am not able to get the required visual I want (range values and the ids in the required range), since the dax is always going to the y-axis and not the x-axis(in bar chart) and not going in legend(in pie chart).
 
What can I do so that I get the ranges? Please Help.

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi John2 ,

    The legend only accepts entity columns/calculated columns as values.
    Please try new a calculated column first:

    Table = 
SUMMARIZE (
    'patch',
    'patch'[computer id],
    "Compliant_%",
        VAR compliant_count =
            SUM ( patch[Compliant Count] )
        VAR applicable_system_count =
            COUNTROWS ( patch )
        RETURN
            DIVIDE ( compliant_count, applicable_system_count, 0 )
)

     and new a calculated column by use [Compliance_catergory] code:



    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi John2 ,

    The legend only accepts entity columns/calculated columns as values.
    Please try new a calculated column first:

    Table = 
SUMMARIZE (
    'patch',
    'patch'[computer id],
    "Compliant_%",
        VAR compliant_count =
            SUM ( patch[Compliant Count] )
        VAR applicable_system_count =
            COUNTROWS ( patch )
        RETURN
            DIVIDE ( compliant_count, applicable_system_count, 0 )
)

     and new a calculated column by use [Compliance_catergory] code:



    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group