cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ngocnguyen
Helper III
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

Y.PNG

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



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!

View solution in original post

5 REPLIES 5
amitchandak
Super User IV
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!

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

 

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

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

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.