cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ddb300
New Member

Combining sheets and Creating a hierarchy

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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @ddb300 ,

I created some data:

Table1:

vyangliumsft_0-1634868706029.png

Table2:

vyangliumsft_1-1634868706032.png

Table3:

vyangliumsft_2-1634868706033.png

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

vyangliumsft_3-1634868706034.png

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:

vyangliumsft_4-1634868706037.png

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

vyangliumsft_5-1634868706037.png

Result

vyangliumsft_6-1634868706039.png

 

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

View solution in original post

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

Hi  @ddb300 ,

I created some data:

Table1:

vyangliumsft_0-1634868706029.png

Table2:

vyangliumsft_1-1634868706032.png

Table3:

vyangliumsft_2-1634868706033.png

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

vyangliumsft_3-1634868706034.png

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:

vyangliumsft_4-1634868706037.png

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

vyangliumsft_5-1634868706037.png

Result

vyangliumsft_6-1634868706039.png

 

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

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors