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
andy_scott42
Helper II
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
       
SiteSales   SitePreOrders
Site A1000   Site A2000
Site B2000   Site B0
Site C500   Site C1000

 

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
parry2k
Super User
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.

View solution in original post

6 REPLIES 6
parry2k
Super User
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.

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] ) )
)
parry2k
Super User
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.

I did download your file and page 4 is blank...

parry2k
Super User
Super User

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

 

site.gif

 

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.

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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