Forum Discussion

Massingc1983's avatar
Massingc1983
Frequent Visitor
2 years ago

Filtered DAX Measure

Hi

 

I am trying to work out a percentage based on filtered measures. The first measure is number of "Active" suppliers. The second is number of those active suppliers who are marked as "High Risk" in another column. Then i would like to divide the two results "Active Suppliers High Risk" by "Active Suppliers" to give a percentage.

 

I have come up witht he below formula however it gives me 100% and i am not sure where i am going wrong. Any help would be appreciated. 

 

Active Supplier ABC =
VAR ActiveSuppliers =
CALCULATE(
    DISTINCTCOUNT('Contract Append'[Supplier name]),
    'Contract Append'[Status] = "Active" )
VAR ABCHighRiskSuppliers =
CALCULATE(ActiveSuppliers, 'Contract Append'[2.2 Modern Slavery Risk Rating] = "High")
Return
DIVIDE(ABCHighRiskSuppliers, ActiveSuppliers)

4 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, Massingc1983 
    try below 

    Active Supplier ABC =
    VAR ActiveSuppliers =
    CALCULATE(
        DISTINCTCOUNT('Contract Append'[Supplier name]),
        'Contract Append'[Status] = "Active" )
    VAR ABCHighRiskSuppliers =
    CALCULATE(
    DISTINCTCOUNT(
    'Contract Append'[Supplier name]), 'Contract Append'[2.2 Modern Slavery Risk Rating] = "High",
    KEEPFILTERS('Contract Append'[Status] = "Active"))
    )
    Return
    DIVIDE(ABCHighRiskSuppliers, ActiveSuppliers)
     
    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly. Appreciate your kudos.
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi  Massingc1983 ,

       

      I don't suggest you to use "ActiveSuppliers" part in "ABCHighRiskSuppliers", use DISTINCTCOUNT() function again in "ABCHighRiskSuppliers". 

      Active Supplier ABC =
VAR _ActiveSuppliers =
    CALCULATE (
        DISTINCTCOUNT ( 'Contract Append'[Supplier name] ),
        'Contract Append'[Status] = "Active"
    )
VAR _ABCHighRiskSuppliers =
    CALCULATE (
        DISTINCTCOUNT ( 'Contract Append'[Supplier name] ),
        'Contract Append'[Status] = "Active",
        'Contract Append'[2.2 Modern Slavery Risk Rating] = "High"
    )
RETURN
    DIVIDE ( _ABCHighRiskSuppliers, _ActiveSuppliers )

      It works on myside. My Sample:

      Result is as below.

       

       

      Best Regards,
      Rico Zhou

       

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