Forum Discussion
YoY Variance in Matrix
I've tried a lot here and can't seem to get anything to work. I basically have a list of agents and their total sales by year... I would like a simple variance column at the end of the matrix that lists the name of the agent, their total sales in each year the given closed months (for example if they choose January through November, it would be the sales in each year for those months). All good with that, but I can't a simple variance (IE subtracting one column from the other)
None of the solutions i'm seeing seem to be working. Would I need to create another table in order to get the visual to work, and what DAX would I need?
2 Replies
Hi Anonymous ,
Depending on the information you provide, you can use column subtraction to achieve one column minus another.
You can provide some non-private detail data or pbix files and state what you want to achieve that I can help you.
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Yifan Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks...here is a look at the style of visual I want to have, where the 'Delta' column would be a subraction of 2023 over 2022. I have dates both in the source table and a calendar table. The Years come from a 'Year' column that I created by extracting from the date. The numbers are sum of sales in those years. The agent names come from the same table
I tried using
This Year = CALCULATE(count(Query1[count]),filter(ALLSELECTED('Calendar'),'Calendar'[Year]=max('Calendar'[Year])))and a similar one for prior year, and then a variance to subtract This Year - Last Year but it doens't seem to work2022 2023 Delta Agent A 14 22 Agent B 31 20 Agent C 16 15 Agent D 29 37