Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi, i need to calculate target vs total sale per date
i have two tables:
1. target
columns:
date | branch | target | key |
6/19/2017 | 4524 | 100 | 6.19.20174524 |
6/20/2017 | 4524 | 200 | 6.20.20174524 |
6/21/2017 | 4524 | 300 | 6.21.20174524 |
6/22/2017 | 4524 | 400 | 6.22.20174524 |
07/01/2017 | 4524 | 1000 | 7.1.20174524 |
2.sales
columns:
date | branch | sale | key |
6/19/2017 | 4524 | 10 | 6.19.20174524 |
6/19/2017 | 4524 | 20 | 6.19.20174524 |
6/19/2017 | 4524 | 30 | 6.19.20174524 |
6/19/2017 | 4524 | 40 | 6.19.20174524 |
6/19/2017 | 4524 | 50 | 6.19.20174524 |
6/20/2017 | 4524 | 30 | 6.20.20174524 |
6/20/2017 | 4524 | 40 | 6.20.20174524 |
6/20/2017 | 4524 | 50 | 6.20.20174524 |
6/20/2017 | 4524 | 60 | 6.20.20174524 |
6/20/2017 | 4524 | 70 | 6.20.20174524 |
6/20/2017 | 4524 | 80 | 6.20.20174524 |
6/20/2017 | 4524 | 90 | 6.20.20174524 |
6/21/2017 | 4524 | 1 | 6.21.20174524 |
6/21/2017 | 4524 | 2 | 6.21.20174524 |
6/21/2017 | 4524 | 3 | 6.21.20174524 |
6/21/2017 | 4524 | 4 | 6.21.20174524 |
6/21/2017 | 4524 | 5 | 6.21.20174524 |
6/21/2017 | 4524 | 6 | 6.21.20174524 |
6/22/2017 | 4524 | 20 | 6.22.20174524 |
6/22/2017 | 4524 | 40 | 6.22.20174524 |
6/22/2017 | 4524 | 60 | 6.22.20174524 |
6/22/2017 | 4524 | 80 | 6.22.20174524 |
6/22/2017 | 4524 | 100 | 6.22.20174524 |
i need to show on power bi :
date | branch | target | key | total sale | delta |
6/19/2017 | 4524 | 100 | 6.19.20174524 | 150 | 50 |
6/20/2017 | 4524 | 200 | 6.20.20174524 | 420 | 220 |
6/21/2017 | 4524 | 300 | 6.21.20174524 | 21 | -279 |
6/22/2017 | 4524 | 400 | 6.22.20174524 | 300 | -100 |
07/01/2017 | 4524 | 1000 | 7.1.20174524 | null | 0 |
how can i do it? i tried to do in the matrix visual with no succes, can someone give me syntax to create relevant calculation?
also' its very importent to show the target even if in there is no value in the sales table per target via key.
thank you.
Solved! Go to Solution.
Based on your sample data, the type column doesn't take effect on your expected result, you can add a type slicer to filter the table visual.
Besides, please check the last screenshot in my first reply.
'At last, create a table visual as follows. Make sure you select “Show items with no data” for date field.'
Regards,
Firstly, in Query Editor of Power BI Desktop, click on target table, then choose “Merge Queries-> Merge Queries as New”.
Secondly, expand the sale column from the NewColumn as shown in the following screenshot.
Thirdly, create the measures below in the merged table.
target value = MAX(Merge1[target])
total sale = SUM(Merge1[NewColumn.sale])
delta = IF([total sale]=BLANK(),0,[total sale]-[target value])
At last, create a table visual as follows. Make sure you select “Show items with no data” for date field.
Regards,
hi,i forgot somthing, in the sales table i have a column named "type"
i need to show only data with the type 1 or 2 .
the type column didnt show on the merge table.
how can i filter only data with specific type? thanks.
In second step, you can expand the sale and type columns from the NewColumn, then you are able to filter the NewColumn.type column to only contain type 1 and type 2 data.
Regards,
ok, and if i whant to show type 1 or 2 and its ok
but if i have a target with no sales its hide it when i applied this filter
what i neeed to do?
@davidi4524,
Could you please post the complete sample data of your sales table and post the expected result here?
Regards,
sales table:
date | branch | sale | key | type of document |
6/19/2017 | 4524 | 10 | 6.19.20174524 | 1 |
6/19/2017 | 4524 | 20 | 6.19.20174524 | 1 |
6/19/2017 | 4524 | 30 | 6.19.20174524 | 1 |
6/19/2017 | 4524 | 40 | 6.19.20174524 | 1 |
6/19/2017 | 4524 | 50 | 6.19.20174524 | 2 |
6/20/2017 | 4524 | 30 | 6.20.20174524 | 2 |
6/20/2017 | 4524 | 40 | 6.20.20174524 | 2 |
6/20/2017 | 4524 | 50 | 6.20.20174524 | 2 |
6/20/2017 | 4524 | 60 | 6.20.20174524 | 2 |
6/20/2017 | 4524 | 70 | 6.20.20174524 | 2 |
6/20/2017 | 4524 | 80 | 6.20.20174524 | 2 |
6/20/2017 | 4524 | 90 | 6.20.20174524 | 2 |
6/21/2017 | 4524 | 1 | 6.21.20174524 | 3 |
6/21/2017 | 4524 | 2 | 6.21.20174524 | 3 |
6/21/2017 | 4524 | 3 | 6.21.20174524 | 3 |
6/21/2017 | 4524 | 4 | 6.21.20174524 | 3 |
6/21/2017 | 4524 | 5 | 6.21.20174524 | 3 |
6/21/2017 | 4524 | 6 | 6.21.20174524 | 4 |
6/22/2017 | 4524 | 20 | 6.22.20174524 | 4 |
6/22/2017 | 4524 | 40 | 6.22.20174524 | 4 |
6/22/2017 | 4524 | 60 | 6.22.20174524 | 4 |
6/22/2017 | 4524 | 80 | 6.22.20174524 | 4 |
6/22/2017 | 4524 | 100 | 6.22.20174524 | 5 |
target table:
date | branch | target | key |
6/19/2017 | 4524 | 100 | 6.19.20174524 |
6/20/2017 | 4524 | 200 | 6.20.20174524 |
6/21/2017 | 4524 | 300 | 6.21.20174524 |
6/22/2017 | 4524 | 400 | 6.22.20174524 |
07/01/2017 | 4524 | 1000 | 7.1.20174524 |
need to show:
date | branch | target | key | total sale | delta |
6/19/2017 | 4524 | 100 | 6.19.20174524 | 150 | 50 |
6/20/2017 | 4524 | 200 | 6.20.20174524 | 420 | 220 |
6/21/2017 | 4524 | 300 | 6.21.20174524 | 21 | -279 |
6/22/2017 | 4524 | 400 | 6.22.20174524 | 300 | -100 |
07/01/2017 | 4524 | 1000 | 7.1.20174524 | null | 0 |
note, that for the target in 7/1/2017 there is no sales data , but i steel need to show him.
thanks.
Based on your sample data, the type column doesn't take effect on your expected result, you can add a type slicer to filter the table visual.
Besides, please check the last screenshot in my first reply.
'At last, create a table visual as follows. Make sure you select “Show items with no data” for date field.'
Regards,
hi, thank you very much for this detaild answer. im gonna try and let you know.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |