Calculate measure based on list of values from another table

[andy_scott42]

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.

[parry2k · Accepted solution]

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

[andy_scott42]

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]

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

[andy_scott42]

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

[parry2k]

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

[andy_scott42]

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.