Summarize - Scenario dependencies between tables

[tonijj]

Hi there community!

Im struggling with a problem that I cant just get my head around. Been trying various angles myself, and searching the forum a lot as well, which has helped me somewhat, but not getting me to my end goal. Hence, here I am asking for help 😊

Problem:

Im trying to create a measure that combines several tables into a total cost, depending on;

• Volume table A or B
• Price Table C or D
• Supplier (G)
• The three tables above combined by the rules set in the Scenario Table (F)

I’m thinking that if I have a slicer on the Scenario Table (F) in my Dashboard, which determines the cost it will show. Cost would be:

 Volume x Prices, for that specific Supplier that is being rewarded that Service, defined in the Scenario table (F)

My current Setup:

Several datatables, such as;

A) Volume Table A

B) Volume Table B

C) Price Table C

D) Price Table D

E) Services Table

F) Scenario Table

G) Supplier Table

Quick description of the tables;

A) Set of volume tables, per Service ( E) that is connected via a unique key called “SIN”.

B) Same as (A), but just with different volumes

C) Prices per service item (E ) also connected via unique “SIN”.

D) Same as above but with different prices per item

E) Service table with several dimensions, for drilldown analysis. Every single item has a “SIN” that is unique

F) Different scenarios in terms of Services (E ) meaning that a supplier will have a different scope of services, depending on the scenario. Lets take an example, as this is the key to my problem;

    F1. Scenario 1 = Supplier A gets Part X of the services, and Supplier B gets Part Y of the service scope.

So, what have I tried you ask? Well, everything but the correct solution I guess 😊

My initial thought was to use “Summarize” combined with a Lookup but never got it to fully work.

Another way was to test “Calculate” and use the Filter function, but with that I wouldn’t be able to use the slicer right? Then I would have to use 3 different measures I suppose.  

I scrubbed some data with the setup in a file that is shared here: FIND IT HERE

[v-yulgu-msft]

Hi @tonijj,

What is your desired output? What visual you want to use to display data in Power BI desktop? Could you please post an image with examples to show us expected result? 

Regards,

Yuliana Gu

[tonijj]

Hi Yuliana,

Thank you for your response!

Its not so much about the visual, but rather the formula to get the desired result (Total Cost).

So, if we look at the Scenario table, which is key for the formula, I want it to do the following:

Create the Total Cost based on the setup below, combining Price X Volume. 

So, in terms, if we take "Setup 2" as example: 

The formula should:

• Look at which supplier will deliver which Services.
• In this case it would be: Google for Legacy Systems and Data Center Services. And Apple for End User Services
• Formula should then lookup Googles price for Legacy Services,  which can be found via Services Table (E) and Price Table (D) - the key combining those tables is the column called "SIN"
• Then it would lookup the volume (Table  A) for respective service, which is also found by using column "SIN". 

 

So if by looking at the example excel file provided, for Setup 2, and only Legacy Systems the result should be (using 2019 as example):

[tonijj]

@v-yulgu-msft

Did the information above help? 

[tonijj]

@v-yulgu-msft

I take that as a no? 🙂 

Anyone else that maybe could lend a helping hand?

[tonijj]

No one with any ideas? 😞