cancel
Showing results for
Did you mean:
Helper III

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

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

1 ACCEPTED SOLUTION
Super User IV

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

5 REPLIES 5
Super User IV

@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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper III

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

Super User IV

@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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper III

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.

Super User IV

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group