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.PPlanned sell date Planned priceBike 18/1/2018100Bike 28/1/2018100Bike 39/1/2018100Bike 410/1/2018100Bike 59/1/2018100Bike 68/1/2018100Bike 79/1/2018100Bike 89/1/2018100Bike 99/1/2018100 ACTUAL:Product.AActual Sell dateActual sell priceBike 68/1/2018100Bike 78/1/2018100Bike 810/1/2018100Bike 99/1/2018100Bike 1010/1/2018100Bike 1110/1/2018100Bike 1210/1/2018100Bike 1310/1/2018100Bike 1410/1/2018100The first table is the Plan. It includes some products, its planned date of sell (refereing the month) and the planned price.The second table is Actuals. it includes the products that were sold, the actual date (month) and the price. Some of the produts appear in both tables, but others dont. eg. some were in the selling plan but didnt get sold, some were in the plan and got sold, and others were sold but not included in the plan. So i've merged the tables with the Merge query (quite useful btw), using as key field the products.And now I have this: Product.PPlanned sell date Planned priceProduct.AActual Sell dateActual sell pricePlan ACHIEVED?Bike 18/1/2018100 1Bike 28/1/2018100 1Bike 39/1/2018100 1Bike 410/1/2018100 1Bike 59/1/2018100 1Bike 68/1/2018100Bike 68/1/20181002Bike 79/1/2018100Bike 78/1/20181002Bike 89/1/2018100Bike 810/1/20181002Bike 99/1/2018100Bike 99/1/20181002 Bike 1010/1/20181003 Bike 1110/1/20181003 Bike 1210/1/20181003 Bike 1310/1/20181003 Bike 1410/1/20181003 So far so good. My goal is to be able to flag each of the scenarios. Adding a new Measure (or column...) called Plan ACHIEVED?, which I would like to report: 1 if the product was in the plan, but NOT sold2 if the product was in the plan and got sold3 if the product was NOT in the plan but got sold (this is very easy to do with simple IF functions)... And here comes the tricky part:I would like to do that using the date period/range that I have selected with a date slicer.i.e. for Bike 7, if I have selected in the slicer only september, it will show that was planned for but not sold in september.but if I select on the slicer a range of dates from aug to oct, i want it show that was planned and sold in the selected period. The bit I am missing is how to do an IF function using the selected range of dates by slicer.Any help? Thanks a lot for your time.

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 priceBike 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 100Bike 201 August 2018 100Bike 301 September 2018 100Bike 401 October 2018 100Bike 501 September 2018 100Bike 601 August 2018 100Bike 701 September 2018 100Bike 801 September 2018 100Bike 901 September 2018 100as 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 ) )

Forum Discussion

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	Planned sell date	Planned price
Bike 1	8/1/2018	100
Bike 2	8/1/2018	100
Bike 3	9/1/2018	100
Bike 4	10/1/2018	100
Bike 5	9/1/2018	100
Bike 6	8/1/2018	100
Bike 7	9/1/2018	100
Bike 8	9/1/2018	100
Bike 9	9/1/2018	100

ACTUAL:

Product.A	Actual Sell date	Actual sell price
Bike 6	8/1/2018	100
Bike 7	8/1/2018	100
Bike 8	10/1/2018	100
Bike 9	9/1/2018	100
Bike 10	10/1/2018	100
Bike 11	10/1/2018	100
Bike 12	10/1/2018	100
Bike 13	10/1/2018	100
Bike 14	10/1/2018	100

The first table is the Plan. It includes some products, its planned date of sell (refereing the month) and the planned price.

The second table is Actuals. it includes the products that were sold, the actual date (month) and the price.

Some of the produts appear in both tables, but others dont. eg. some were in the selling plan but didnt get sold, some were in the plan and got sold, and others were sold but not included in the plan.

So i've merged the tables with the Merge query (quite useful btw), using as key field the products.

And now I have this:

Product.P	Planned sell date	Planned price	Product.A	Actual Sell date	Actual sell price	Plan ACHIEVED?
Bike 1	8/1/2018	100				1
Bike 2	8/1/2018	100				1
Bike 3	9/1/2018	100				1
Bike 4	10/1/2018	100				1
Bike 5	9/1/2018	100				1
Bike 6	8/1/2018	100	Bike 6	8/1/2018	100	2
Bike 7	9/1/2018	100	Bike 7	8/1/2018	100	2
Bike 8	9/1/2018	100	Bike 8	10/1/2018	100	2
Bike 9	9/1/2018	100	Bike 9	9/1/2018	100	2
			Bike 10	10/1/2018	100	3
			Bike 11	10/1/2018	100	3
			Bike 12	10/1/2018	100	3
			Bike 13	10/1/2018	100	3
			Bike 14	10/1/2018	100	3

So far so good.

My goal is to be able to flag each of the scenarios. Adding a new Measure (or column...) called Plan ACHIEVED?, which I would like to report:

1 if the product was in the plan, but NOT sold

2 if the product was in the plan and got sold

3 if the product was NOT in the plan but got sold

(this is very easy to do with simple IF functions)...

And here comes the tricky part:

I would like to do that using the date period/range that I have selected with a date slicer.

i.e. for Bike 7, if I have selected in the slicer only september, it will show that was planned for but not sold in september.

but if I select on the slicer a range of dates from aug to oct, i want it show that was planned and sold in the selected period.

The bit I am missing is how to do an IF function using the selected range of dates by slicer.

Any help?

Thanks a lot for your time.

Need Help

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:

Product	Date	Actual Price	Planned price
Bike 6	01 August 2018	100
Bike 7	01 August 2018	100
Bike 8	01 October 2018	100
Bike 9	01 September 2018	100
Bike 10	01 October 2018	100
Bike 11	01 October 2018	100
Bike 12	01 October 2018	100
Bike 13	01 October 2018	100
Bike 14	01 October 2018	100
Bike 1	01 August 2018		100
Bike 2	01 August 2018		100
Bike 3	01 September 2018		100
Bike 4	01 October 2018		100
Bike 5	01 September 2018		100
Bike 6	01 August 2018		100
Bike 7	01 September 2018		100
Bike 8	01 September 2018		100
Bike 9	01 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
    )
)

4 Replies

Stachu

Community Champion

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:

Product	Date	Actual Price	Planned price
Bike 6	01 August 2018	100
Bike 7	01 August 2018	100
Bike 8	01 October 2018	100
Bike 9	01 September 2018	100
Bike 10	01 October 2018	100
Bike 11	01 October 2018	100
Bike 12	01 October 2018	100
Bike 13	01 October 2018	100
Bike 14	01 October 2018	100
Bike 1	01 August 2018		100
Bike 2	01 August 2018		100
Bike 3	01 September 2018		100
Bike 4	01 October 2018		100
Bike 5	01 September 2018		100
Bike 6	01 August 2018		100
Bike 7	01 September 2018		100
Bike 8	01 September 2018		100
Bike 9	01 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
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
  Community 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)

v-lili6-msft
Community Support
7 years ago
HI, MeloKutman
For your requirement, you want to use the date period/range with a date slicer to affect the measure.
however, Planned sell date and Actual Sell date are two different columns in the merge table, so it could be achieved
by one slicer. It must be in one column. Therefore, if you need a measure you need use Append tables
Stachu's solution works well for Measure. and you could do these to use a combination of them as below:

here is my improved method.
Step1:
Add an append table and the measure as above.
Step2:
Add a Product column for merge table by this
Product = IF(ISBLANK(Merge1[Product.P]),Merge1[ACTUAL.Product.A],Merge1[Product.P])
Step3:
Create the relationship between merge table and append table
Step4:
Create visuals like this
Result:
here is pbix, please try it.
https://www.dropbox.com/s/3ocguss7o371c0e/Merge%20tables%20and%20measure%20based%20on%20selected%20range.pbix?dl=0

Best Regards,
Lin

Forum Discussion

Merge tables and measure based on selected range

4 Replies

Related content

Table selection

DAX Measure Not Showing Correct Slicer State (All Selected, No Selection, Partial Selection)

DAX Measure ignore filter/slicer selection

Set measure value based on slicer selection

Show measure values till the month selected