Combining sheets and Creating a hierarchy

[ddb300]

I am working on creating a model using an Excel file with 3 different sheets. This is to help a store price their products. Sheet 1 contains 3 columns, Location, Price, and When it was updated (it is either within 30 days, or Not within 30 days) Sheet 2 contains 2 columns, locations (Similar to sheet one but they are from another company) and the price they sell the product for. Sheet 3 contains similar locations and the estimated price of the product. My goal is to create a query where We select a location, and it will take the price in order of the rules and show what sheet it is from. (If a price is “Null” or “Blank” it will move to the next rule). I only want it showing me one price.

The Hierarchy of which price I want it to show are as followed:

Sheet 1 Price (and to tell me if it was updated within 30 days or not) à if this price is unavailable, it will show me the price for the same location from sheet 2. à if this is unavailable it will give me the price for sheet 3.

If possible, I would like to add another column to my results being, the average of all of them weather there is 1 price available or all 3.

HOW CAN I DO THIS???

[v-yangliu-msft · Accepted solution]

Hi  @ddb300 ,

I created some data:

Table1:

Table2:

Table3:

Here are the steps you can follow：

Will it be updated within 30 days:

Create measure.

measure_update =
var _update=SELECTEDVALUE('Table1'[Location])
return
CALCULATE(MAX('Table1'[Update]),FILTER(ALL('Table1'),'Table1'[Location]=_update))

Price hierarchy:

Create measure.

Price hierarchy =
var _location=SELECTEDVALUE('Table1'[Location])
var _price1=CALCULATE(MAX('Table1'[Price]),FILTER(ALL('Table1'),'Table1'[Location]=_location))
var _price2=CALCULATE(MAX('Table2'[Price]),FILTER(ALL('Table2'),'Table2'[Location]=_location))
var _price3=CALCULATE(MAX('Table3'[Price]),FILTER(ALL('Table3'),'Table3'[Location]=_location))
return
IF(
    _price1<>"NULL",_price1,
    IF(
        _price2<>"NULL",_price2,
        IF(
        _price3<>"NULL",_price3,BLANK())))

When the prices in Table 1 and Table 2 are both NULL, the prices in Table 3 will be displayed directly:

Price available:

Create a calculated column in each table:

table1_count =
COUNTX(FILTER('Table1','Table1'[Price]<>"NULL"&&'Table1'[Location]=EARLIER('Table1'[Location])),[Price])

table2_count =
COUNTX(FILTER('Table2','Table2'[Price]<>"NULL"&&'Table2'[Location]=EARLIER('Table2'[Location])),[Price])

table3_count =
COUNTX(FILTER('Table3','Table3'[Price]<>"NULL"&&'Table3'[Location]=EARLIER('Table3'[Location])),[Price])

Create measure.

price available =
var _location=SELECTEDVALUE('Table1'[Location])
var _price1=CALCULATE(SUM('Table1'[table1_count]),FILTER(ALL('Table1'),'Table1'[Location]=_location))
var _price2=CALCULATE(SUM('Table2'[table2_count]),FILTER(ALL('Table2'),'Table2'[Location]=_location))
var _price3=CALCULATE(SUM('Table3'[table3_count]),FILTER(ALL('Table3'),'Table3'[Location]=_location))
return
_price1+_price2+_price3

Result：

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly