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?

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

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 .

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.

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.

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

Proud to be a Super User!

Announcements