Compare with Target that don't have same logic with actual

[ngocnguyen]

Hi,

I have 2 table as belows. Table 1 is actual result by month, by customer, by zone .    Table 2 is target

I wanna create 2 slicer: Month and Customer  => when I choose slicer Month for Jan-21  and Feb-21  & Slicer Customer A,   I will get the results as table Results

Customer A, zone X:  Actual = Jan-21 actual + Feb-21 Actual   

Customer A, zone Y: Actual = Jan-21 actual + Feb-21 Actual   

Customer A, zone Total: Actual = Customer A, zone X  (Actual)  + Customer A, zone Y (Actual) 

For target table, it not follows the above logic. It just get data from Table 2 only

So, What Dax formulas can I use to get the results?

Many thanks for your support

[amitchandak]

@ngocnguyen , Create common dimesnion for customer, Zone and date. Join first two with both tables. And date with onlt actual.

Take customer and zone from common tables and date slicer from date table and take actual and target from table 1 and Table 2 it should work .

refer common table - https://www.youtube.com/watch?v=Bkf35Roman8

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[ngocnguyen]

@amitchandak 

If I create the dimention as you suggest, so how about the bottom line ( customer A,  zone: Total) , I don't know how it will work ? cus the actual of this line = total customer A , but the Target is just get from table 2

Actual customer A =10+11+9+10

but, Target customer A (15) # {Target customer A zone X  (12)+ Target customer A zone Y (9) }

[amitchandak]

@ngocnguyen , create a summarize table from Table 1, Where Zone is Total, Join this also with join zone, Customer and date.

Create an actual formula which sum of actual from table1 and this new table

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[ngocnguyen]

@amitchandak 

My account not allow me to attached file here. Could you pls support me to make 1 sample of it as pictures in my post. 

I still not yet get your point :(((((((. I am really newbies in PBI.

Sorry for bothering you in advance. 

[amitchandak · Accepted solution]

@ngocnguyen , check this

a new table

table3= summarize(Table1, Table1[month], Table1[customer], "Zone", "Total", "Actual", sum(Table1[Actual])) // If you have date use date in place of month
//Join this with Zone, Customer and date

new measure

Final Actual = Sum(Table1[Actual]) +Sum(Table3[Actual])