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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BruceSV
New Member

Ensuring that my measure also applies the filter

Hi everyone, 

 

I am trying to calculate the utilization of a team of people per period. But I am stuck with an error. 

 

I have two tables. One fact table that holds the time_spent value and a dimensions table that holds the total_capacity of the team member value.

 

The calculation is fairly straigth forward: 

(time_spent / total_capacity) * 100%

 

So my dax measure is as follows:

 

Utilization Percentage = CALCULATE(
    DIVIDE(
        SUM(tbl_1[time_spent]),
        SUM(tbl_2[total_capacity])), 
        ALLSELECTED('tbl_1')
    *100)

 

However this is giving me an "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

 

If I remove the "ALLSELECTED" clause the code works but the measure does not take some filters into consideration. Giving me incorrect information.

 

What I essentially want is a measure that calculates the utilization % but still respects all the applied filters on the visual.

1 ACCEPTED SOLUTION
ERD
Super User
Super User

@BruceSV , I think you've put *100 in the wrong place.. 

Utilization Percentage =
CALCULATE (
    DIVIDE ( SUM ( tbl_1[time_spent] ), SUM ( tbl_2[total_capacity] ) ) * 100,
    ALLSELECTED ( 'tbl_1' )
)

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

4 REPLIES 4
ERD
Super User
Super User

@BruceSV , I think you've put *100 in the wrong place.. 

Utilization Percentage =
CALCULATE (
    DIVIDE ( SUM ( tbl_1[time_spent] ), SUM ( tbl_2[total_capacity] ) ) * 100,
    ALLSELECTED ( 'tbl_1' )
)

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Although the syntax is approved the results are not what I expected. The measure now doesnt apply the legend anymore. 

 

Edit - It actually did work and surfaces a different problem, need to update my data model. Thanks @ERD 

 

some_bih
Super User
Super User

Hi @BruceSV ALLSELECTED is most compex function in DAX.

Usually you should have  some aggregation in case of this error.

My question to you why in your ALLSELECTED you put table_1 as in CALCULCATE there are also table_2 and why it is *100) at end? Did you try pure ALLSELECTED ()?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Hi @some_bih - I have tried this but that doesn't work unfortunately.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.