Combining sheets and Creating a hierarchy

Question

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???

anonymous · Accepted Answer

Hi&nbsp; ddb300&nbsp;,
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&lt;&gt;"NULL",_price1,
    IF(
        _price2&lt;&gt;"NULL",_price2,
        IF(
        _price3&lt;&gt;"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]&lt;&gt;"NULL"&amp;&amp;'Table1'[Location]=EARLIER('Table1'[Location])),[Price])table2_count =
COUNTX(FILTER('Table2','Table2'[Price]&lt;&gt;"NULL"&amp;&amp;'Table2'[Location]=EARLIER('Table2'[Location])),[Price])table3_count =
COUNTX(FILTER('Table3','Table3'[Price]&lt;&gt;"NULL"&amp;&amp;'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