cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anthony56 Frequent Visitor
Frequent Visitor

DAX formula help with ALL or ALLEXCEPT

I'm somewhat new to complex DAX formulas, and would like some guidance on filter contexts. Specifically, we have some data where the values are associated with User 1, but the amounts need to be split between User 1 and User 2. So, for example, in the data below, while Apple and Orange are associated to user Mike, and Pear is associated to Julie, I need some way to "allocate" half of Orange to Mike and half to Julie.

 

UserGroupAmount
MikeApple5
MikeOrange8
JuliePear6

 

Thus, by "allocating" half to each, the total amounts would be: Mike = 9 (5 Apple + 4 half Orange), and Julie = 10 (6 Pear + 4 half Orange).

UserTotal
Mike9
Julie10

 

Each time I try to create a visualization, the data is grouped by Mike and Julie, and I can't figure out how to get the Orange to be partially "allocated" to Julie. I've tried using ALL and ALLEXCEPT to remove the filter context in my measure, but I can't figure out how to make this work. Thanks for any guidance you can offer.

1 ACCEPTED SOLUTION

Accepted Solutions
anthony56 Frequent Visitor
Frequent Visitor

Re: DAX formula help with ALL or ALLEXCEPT

To follow up, here is how I solved this - not sure if this is correct, but it worked, so let me know if anyone suggests a better approach.

 

I created a new table with a "factor" to apply to the Orange value, where Mike has -0.5 and Julie has +0.5, like this:

New table: Factor = DISTINCT( VALUES( UserData[User] ) )

New column: OrangeFactor = SWITCH( [User], "Mike", 0.5, "Julie", -0.5)

This produced a new table that looked like this:

Mike, -0.5

Julie, 0.5

 

I then created a relationship between my original UserData table and this new Factor table, joined on the User column.

 

I then created a new Measure on the original UserData table that calculated the "Orange adjustment", like this:

Orange Adjustment = SUM(Factor[OrangeFactor]) * CALCULATE(SUMX(FILTER(ALL(UserData), [Group]="Orange"), [Amount]))

This essentially takes the total of all data where Group="Orange" and multiplies it by the Factor for the User, which results in -4 for Mike, and +4 for Julie.

 

I then created a new Measure that is the Adjusted Amount, which takes the sum of Amount, and adds the Orange adjustment, like this:

Adj Amount = SUM(UserData[Amount]) + [Orange Adjustment] 

 

This produces the following results:

PowerBI-1.png

 

Let me know if there is a cleaner or better way to do this?

2 REPLIES 2
Community Support Team
Community Support Team

Re: DAX formula help with ALL or ALLEXCEPT

HI @anthony56,

 

I'd like to suggest you refer to below blogs about all and allexcept functions:

ALL, ALLEXCEPT and VALUES in DAX

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
anthony56 Frequent Visitor
Frequent Visitor

Re: DAX formula help with ALL or ALLEXCEPT

To follow up, here is how I solved this - not sure if this is correct, but it worked, so let me know if anyone suggests a better approach.

 

I created a new table with a "factor" to apply to the Orange value, where Mike has -0.5 and Julie has +0.5, like this:

New table: Factor = DISTINCT( VALUES( UserData[User] ) )

New column: OrangeFactor = SWITCH( [User], "Mike", 0.5, "Julie", -0.5)

This produced a new table that looked like this:

Mike, -0.5

Julie, 0.5

 

I then created a relationship between my original UserData table and this new Factor table, joined on the User column.

 

I then created a new Measure on the original UserData table that calculated the "Orange adjustment", like this:

Orange Adjustment = SUM(Factor[OrangeFactor]) * CALCULATE(SUMX(FILTER(ALL(UserData), [Group]="Orange"), [Amount]))

This essentially takes the total of all data where Group="Orange" and multiplies it by the Factor for the User, which results in -4 for Mike, and +4 for Julie.

 

I then created a new Measure that is the Adjusted Amount, which takes the sum of Amount, and adds the Orange adjustment, like this:

Adj Amount = SUM(UserData[Amount]) + [Orange Adjustment] 

 

This produces the following results:

PowerBI-1.png

 

Let me know if there is a cleaner or better way to do this?

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 260 members 2,561 guests
Please welcome our newest community members: