Forum Discussion

MeloKutman's avatar
MeloKutman
Frequent Visitor
7 years ago
Solved

Merge tables and measure based on selected range

Hi all,   I am trying to do something simple (I guess) but a bit tricky and I cannot find the rigth solution.    I have two different tables (these are a simplification...). PLAN: Product.P ...
Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    I'd actually model it a bit differently, using Append (the product & date need to have the same name for append to work like this), so in the end I would have this table:

     

    ProductDateActual PricePlanned price
    Bike 601 August 2018100 
    Bike 701 August 2018100 
    Bike 801 October 2018100 
    Bike 901 September 2018100 
    Bike 1001 October 2018100 
    Bike 1101 October 2018100 
    Bike 1201 October 2018100 
    Bike 1301 October 2018100 
    Bike 1401 October 2018100 
    Bike 101 August 2018 100
    Bike 201 August 2018 100
    Bike 301 September 2018 100
    Bike 401 October 2018 100
    Bike 501 September 2018 100
    Bike 601 August 2018 100
    Bike 701 September 2018 100
    Bike 801 September 2018 100
    Bike 901 September 2018 100

    as you want the values to be dynamic depending on the slicer you need to use a measure (column is always static):

    Plan Achieved = 
    VAR Planned = MAX(Data[Planned price])
VAR Actual = MAX(Data[Actual Price])
RETURN
IF(
    ISBLANK(Planned),
    3,
    IF(
        ISBLANK(Actual),
        1,
        2
    )
)

     

MeloKutman's avatar
MeloKutman
Frequent Visitor
7 years ago

Thanks Stachu and v-lili6-msft

 

I am trying Stachu's suggestion first. It seems to be working fine. But now I need to include a % for the (total price we planned) vs (the total of actual sold products) for the period, using the categories defined by the Plan achieved measure.

 

Like 2 vs (1+2)... and so on, but it is not working. 
I am using sumx and filter to create a new measure, like this:

  

Plan-and-sold = SUMX(FILTER(Append1,Append1[Plan Achieved]=2),[Price])
/ (SUMX(filter(Append1,Append1[Plan Achieved]=1),[Price])+SUMX(filter(Append1,Append1[Plan Achieved]=2),[Price]))

 

((*I m using the same fields names for the Price in both original tables so they are now "combined".)) 

 

but I get "Blank" as a result.

 

What am I missing here? 

Thanks a lot

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

I think SUMMARIZE per product is more appropiate here cause the [Plan Acieved] only makes sense for a given granularity
try this code - I'm not clear what's the sytnax for your [Price] measure, but hopefully it will work

Measure = 
VAR Tab = ADDCOLUMNS(SUMMARIZE(Append1,Append1[Product]),"Status",[Plan Achieved],"AggPrice",[Price])
VAR One = SUMX(FILTER(Tab,[Plan Achieved]=1),[AggPrice])
VAR Two = SUMX(FILTER(Tab,[Plan Achieved]=2),[AggPrice])
RETURN
DIVIDE(Two,One+Two)