Dynamic calculation

[Anonymous]

Hi team,

I have the following structure and need to apply custom calculation= Cost * Quantity depending of slicer selection.

Cost is in one table, Quantity in another table.

The relationship isn't made in PBI beacuse Cost table has always 2 rows for every ProductKey with the scope to avoid duplicate data.

How can we calculate Cost *Quantity having these if any selection for General and for Service.

Table slicer:

SiteId	Flag
GENERAL	1
SERVICE	0

Table Cost value:

ProductKey		SiteId		Cost
10		GENERAL		100
10		SERVICE		200
20		GENERAL		300
20		SERVICE		400

Table Quantity & expected result to apply:

					expected if slicer selection = GENERAL	expected if slicer selection = SERVICE
ProductKey		WarehouseLocation	Quantity	Cost * Quantity
10		120		5	'=100 * 5	'=200*5
10		115		10	'=100*10	'=200*10
20		150		15	'=300*15	'=400*15

Please could you please be kind and help.

Regards,

Carmen

[Anonymous · Accepted solution]

Hi @Anonymous,

You can create a what-if parameter and use it as the source of the slicer, then you can write a measure formula based on filter selection and current cost values to calculate.

SELECTEDVALUE function - DAX | Microsoft Docs

Use what-if parameters to visualize variables - Power BI | Microsoft Docs

Regards,

Xiaoxin Sheng