Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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?

2 ACCEPTED SOLUTIONS

@Anonymous you can do something like this

 

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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

Anonymous
Not applicable

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() )
)

View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

@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. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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?

@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' 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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?

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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() )))

@Anonymous you can do something like this

 

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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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 glad to help. on other note, you marked your own reply as solution . 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.