cancel
Showing results 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

Helper II

## Calculate measure based on list of values from another table

I have two tables, Sales and PreOrders, with two measures Total Sales and Total PreOrders

 Sales PreOrders Site Sales Site PreOrders Site A 1000 Site A 2000 Site B 2000 Site B 0 Site C 500 Site C 1000

Total Sales = Sum (Sales[Sales]), Total PreOrders = Sum(PreOrders[PreOrders])

I have a slicer with two values; Sales, Sales and PreOrders

When "Sales" is selected I want [Total Sales]

When "Sales and PreOrders" is selected in my slicer I want calculate [Total Sales] + [Total PreOrders] but I want to overwrite [Total Sales] with [Total PreOrders] if it exists in the PreOrders table.

So in the above example, when I select Sales from my Slicer I want the value 3500 (1000+ 2000 + 500), when I select Sales and PreOrders I want 5000 (2000 + 2000 + 1000), as Site A and Site C have PreOrder values.

I know I need to create a list of the Sites from my PreOrders table and then exclude those values from the Total Sales Measure.

I have tried this but I get the error "A table of mutliple values is values was supplied when a single value was expected"

Total Sales and PreOrders =
VAR ListSites =
CALCULATE (
VALUES ( 'PreOrders'[Site] ),
FILTER ( 'PreOrders', [Total PreOrders] > 0 )
)
RETURN
CALCULATE (
[Total Sales] + [Total PreOrders],
FILTER ( Sales, NOT (Sales[Site] IN { ListSites } ))
)

Any help is greatly appreciated.

1 ACCEPTED SOLUTION
Super User

@andy_scott42 attached again, check it out.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

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.

6 REPLIES 6
Super User

@andy_scott42 attached again, check it out.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

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.

Helper II

Thanks @parry2k. The measure worked exactly as I need. I'll post the code below for anyone who doesn't want to download the file

Site Sales =
SWITCH (
SELECTEDVALUE ( 'Site Slicer'[Slicer] ),
"Sale", [Sum Sales],
"Preorders", [Sum Preorders],
SUMX ( VALUES ( Site[Site] ), IF ( COALESCE ( [Sum Preorders], 0 ) = 0, [Sum Sales], [Sum Preorders] ) )
)
Super User

@andy_scott42 Did you downloaded the file? Did you looked at the table and page 4 in the file which has table and slicer. Not sure what else you are looking for? Whatever question you posted, I have provided you the solution. If this doesn't what you are looking for then provide more details.

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.

Helper II

Super User

@andy_scott42 solution attached, look at the tables start with Site, ignore other tables in the file.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

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.

Helper II

Thanks for sharing your PBIX file @parry2k but I can't see the images you have GIFed above, or have you explained how to create it....

This is simplified version of the actual problem I am trying to solve so need to use a Measure to solve it.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors