topic Re: YoY Variance in Matrix in DAX Commands and Tips

YoY Variance in Matrix

Anonymous — Thu, 14 Dec 2023 20:28:31 GMT

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?

Re: YoY Variance in Matrix

Anonymous — Mon, 18 Dec 2023 08:30:59 GMT

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

Re: YoY Variance in Matrix

Anonymous — Mon, 18 Dec 2023 15:00:21 GMT

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 work

	2022	2023	Delta
Agent A	14	22
Agent B	31	20
Agent C	16	15
Agent D	29	37