Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 ) )
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.
Best Regards,
Lin
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 ) )
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
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)
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |