dynamic diff calculation row in matrix

[IDAX]

Dear Power BI Gurus !

I'm struggling for some time already with creating of dynamic calculation which would return difference between selected rows in matrix. Here's a sample of raw data:

Then, matrix should enable to select Foorecast ID in slicer while the calculation row should indicate a diff (Forecast variance) between last and previous forecast:

I tried to use measures for each Forecast ID and it worked. It calculates the diff but if I select two particular Forecast IDs I get all measures rows visible in matrix (selected ones are with values and not selected ones with empty cells). What I need is to visualize only selected Forecast IDS and Forecast variance row at the end.

Do You have a smart solution for it ?

I'd appreciate Your support.

Have a great day !

[Anonymous]

Hi, @IDAX 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

[lbendlin]

What do you need the slicer for?

[IDAX]

The slicer will be selecting Forecast ID

[lbendlin]

But then you are showing all forecast IDs in the visual.  There's no need for the slicer?

[IDAX]

I'm sorry for not being precise enough. There will be more than 2 Forecast IDs. So I need to have a possibility to dynamically see what are the Actuas and two other Forecast IDs (latest one vs. previous one).

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[IDAX]

here's the sample data:

Forecast ID	Company	Product type	Date	Qty
Actuals	Comp1	A	Jan 2024	8
Actuals	Comp1	A	Feb 2024	6
Actuals	Comp1	B	Apr 2024	5
Actuals	Comp1	A	Feb 2024	2
Actuals	Comp2	Y	Jan 2024	10
Actuals	Comp2	X	Jan 2024	4
Actuals	Comp2	X	Mar 2024	10
Actuals	Comp2	X	Jan 2024	5
Actuals	Comp3	Z	May 2024	6
Forecast 1	Comp1	A	Jan 2024	10
Forecast 1	Comp1	B	Jan 2024	15
Forecast 1	Comp2	Y	Jan 2024	5
Forecast 1	Comp2	X	Jan 2024	2
Forecast 1	Comp2	X	Feb 2024	7
Forecast 1	Comp2	Y	Apr 2024	10
Forecast 1	Comp2	X	Apr 2024	5
Forecast 2	Comp2	Y	May 2024	10
Forecast 1	Comp3	Z	Apr 2024	3
Forecast 2	Comp1	B	Jan 2024	11
Forecast 2	Comp1	A	Feb 2024	3
Forecast 2	Comp1	A	Feb 2024	10
Forecast 2	Comp2	X	Jan 2024	6
Forecast 2	Comp2	X	Feb 2024	5
Forecast 2	Comp2	X	Mar 2024	4
Forecast 2	Comp2	Y	Jan 2024	4
Forecast 2	Comp3	Z	Apr 2024	10
Forecast 3	Comp2	Y	May 2024	6
Forecast 3	Comp3	Z	Apr 2024	6
Forecast 3	Comp1	B	Jan 2024	6
Forecast 3	Comp1	A	Feb 2024	10
Forecast 3	Comp1	A	Feb 2024	12
Forecast 3	Comp2	X	Jan 2024	10
Forecast 3	Comp2	X	Feb 2024	4
Forecast 3	Comp2	X	Mar 2024	4
Forecast 3	Comp2	Y	Jan 2024	4
Forecast 3	Comp3	Z	Apr 2024	6

Then in the matrix I would like to see following information as in below sample:

sample 1				date hierarchy
			Q1
			Jan	Feb	Mar	Total
Company	Product type	Forecast ID
Comp2
	X
		Actuals	9		10	19
		Forecast 1	2	7		9
		Forecast 2	6	5	4	15
		Forecast variance	4	-2	4	6
				date hierarchy
sample 2
			Q1
			Jan	Feb	Mar	Total
Company	Product type	Forecast ID
Comp2
	X
		Actuals	9		10	19
		Forecast 1	2	7		9
		Forecast 3	10	4	4	18
		Forecast variance	8	-3	4	9

By using a slicer I would like to be able to pick whatever Forecast ID I want, visualize them in rows (as shown in sample) and the last row would be the dynamic one measuring a diff between those two Forecast IDs. Forecast ID called "Actuals" will be visualized in first row but only as a reference and not to be taken into any calculation.

I hope my explanation is clear enough.

Thank You for any suggestions and ideas.

[lbendlin]

Still not convinced.  I think a graphical solution is simpler.

[IDAX]

I agree 🙂

But that is a requirement I need to fullfil. The table with dynamic comparison of selected Forecast IDs is a "must".

[lbendlin]

I hope someone else can help you further.

[IDAX]

Thanks anyway !