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://communi...
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() )
    )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

parry2k So I've entered :

 

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] ),
ALLSELECTED() ))
 
And I'm still getting 87%, not sure how it keeps coming up?
parry2k's avatar
parry2k
Super User
6 years ago

Anonymous can you share pbix file, remove sensitive info before sharing and  will take a look.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    parry2k Error on my part. Apologies.I had a filter applied to only select a certain grade which I didn't apply to the DAX query. Is there a way to add another filter for e.g. for grades E2, E3, E3L,& R2,R3,R3L & U2,U3, U3L

     

    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] = "E2")
    ),
    CALCULATE(
    COUNT(Sheet1[Company_Id] ),
    ALLSELECTED() )))
    • parry2k's avatar
      parry2k
      Super User
      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
        Not applicable
        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() )
        )