Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Divide with Filter

Quick question, I'm trying to get the % of cold weight cattle by a particular weight (between 16 & 21 kg) I've followed the divide by filter as indicated in the following forum thread:https://community.powerbi.com/t5/Desktop/Divide-With-a-Filter/m-p/74842#M31094

 

and ended up with this DAX formula: 

Cold Weight = DIVIDE(CALCULATE(COUNT(Sheet1[Company_Id]),filter(Sheet1,Sheet1[Intake_Data_Cold_Weight] >=16 && Sheet1[Intake_Data_Cold_Weight] <=21),CALCULATE(COUNT(Sheet1[Company_Id]))))

 

Yet my DAX formula keeps brining up an error message: "Something's wrong with one or more fields: (Sheet1) Cold Weight: Too few arguments were passed to the DIVIDE function. The minimum argument count for the function is 2.

 

Can anyone help?

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous you can do something like this

     

    FILTER(Sheet1,Sheet1[Intake_Data_Grade] IN {"E2","code", "Code", "Code", "Code"})

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    Got it! Thank you so much! I owe you one!

     

    Cold Weight =
    DIVIDE(
    CALCULATE(
    COUNT(Sheet1[Company_Id]),
    filter(
    Sheet1,
    Sheet1[Intake_Data_Cold_Weight] >=16 &&
    Sheet1[Intake_Data_Cold_Weight] <=21
    ),FILTER(Sheet1,Sheet1[Intake_Data_Grade] in {"E2","E3","E3L","R2","R3","R3L","U2","U3","U3L"})
    ),
    CALCULATE(
    COUNT(Sheet1[Company_Id] ),
    ALLSELECTED() )
    )

9 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous here is the fix in your DAX

     

    old Weight = 
DIVIDE(
CALCULATE(
COUNT(Sheet1[Company_Id]),
filter(
Sheet1,
Sheet1[Intake_Data_Cold_Weight] >=16 && 
Sheet1[Intake_Data_Cold_Weight] <=21
)
),
CALCULATE(
COUNT(Sheet1[Company_Id])
)
)

    , you are missing one bracket before 2nd calculate and there is one extra at the end.

     

    Would appreciate Kudos 🙂 if my solution helped. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yeah that worked but it's presented me with another problem.

       

      When I use my original calculate filter :

      Cold Weight = CALCULATE(COUNT(Sheet1[Company_Id]),filter(Sheet1,Sheet1[Intake_Data_Cold_Weight] >=16 && Sheet1[Intake_Data_Cold_Weight] <=21))
       
      I get 157,269 results out of a total company id of 239,323 = 65.7%
       
      Where the formula you so kindly gave me gives me 87%, do I have something in the wrong order?
      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Anonymous I think your 2nd part of the formula is wrong

         

        CALCULATE ( COUNT(Sheet1[Company_Id] ), ALLSELECTED() )

         

        Believe you want divide by count of all compny ids'