Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Please can someone help? Trying to compare my target table to actual sales.
I'm looking to create a calculated column in my target table that shows the actual sales. I think I need something that will calculate:
Sum total sales, group by month and then by contract
Each contract has a target for each month in my target table, it looks like this
Contract | Date | Target | Actual |
1 | Jan 2020 | 100 | |
1 | Feb 2020 | 50 | |
1 | March 2020 | 70 | |
2 | Jan 2020 | 150 | |
2 | Feb 2020 | 200 | |
2 | March 2020 | 250 | |
3 | Jan 2020 | 300 | |
3 | Feb 2020 | 100 | |
3 | March 2020 | 150 |
Solved! Go to Solution.
Hi @Aimeeclaird ,
If I got it correctly, you can create this calculated column to calcuate actual sales for each month:
Actual =
CALCULATE(
SUM('Table'[Target]),
FILTER(
ALL('Table'),
'Table'[Date] = EARLIER('Table'[Date])
)
)
Variance = [Target] - [Actual]
Attached a sample file in the below, hopes to help you.
If I got it wrong, please feel free to let me know so that I could help you better to solve this issue.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Aimeeclaird ,
If I got it correctly, you can create this calculated column to calcuate actual sales for each month:
Actual =
CALCULATE(
SUM('Table'[Target]),
FILTER(
ALL('Table'),
'Table'[Date] = EARLIER('Table'[Date])
)
)
Variance = [Target] - [Actual]
Attached a sample file in the below, hopes to help you.
If I got it wrong, please feel free to let me know so that I could help you better to solve this issue.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Aimeeclaird ,
You can use append both tables (keeping exactly the same name for columns on both tables). So you will have the target and sales on the same table.
Hi @camargos88
If I append, how would that sum the total sales? I want my target table to have a row per contract, per month.
I then want to see the total sales for that contract and month beside it.
Please can you clarify (sorry, PBI is new to me) how appending the sales would allow me to present the target vs the actual? If i append the sales to the target, my targets go up massively as they're duplicated for every sales row?
Many thanks in advance
Aimee
I can think about 2 options:
1) You can group your sales table by month/contract summing the sales values and merge this table with the target table. Be aware of the join kind (I would say full join) and clean the month/contract columns before the merge.
2) Append both tables, cleaning the month/contract values and same column names for them.
The 2 option can be useful if you need to counting the values or calculate avg basead on user selections. If you aggregate you tables having 1 row per month/contract maybe you lose this info.
I'm sorry @camargos88 , I am not sure if I am explaining what I need correctly, or my lack of PBI knowledge is the problem but I don't understand how either of your ideas help.
My final product should show:
- Month Year
- Total Sales (actual)
- Total Target Sales (target)
- Variance between (target - actual)
Also, how should I merge or apend as there isn't a common ID etc?
Is there not a suitable calculation I could write into the target table that 'groups' the sum? So instead of summing the entire column, it looks at the Contract and the Date and gives me the total sales for that month/year and contract?
Check the attached file.