How to replicate the result in a measure if there's no value in the column of the dataset

[PBI_newuser]

Hi, I have a dataset as below, and I created a measure to calculate the usage %. However, there is no Activity C in Warehouse W2, but I want the final output to show the same result as Activity A in Warehouse W2, how to create the measure?

Usage % = Item Used / Item Count

Activity	Warehouse	Item Count	Item Used	Usage %
A	W1	1000	500	50%
B	W1	200	20	10%
C	W1	1050	240	23%
A	W2	1313	344	26%
B	W2	4535	121	3%

Final Output:

Warehouse	Activity	Usage %
W1	A	50%
	B	10%
	C	23%
W2	A	26%
	B	3%
	C	26%

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[PBI_newuser]

Hi @Ashish_Mathur , we have many warehouses and acitivities in the dataset. But for certain warehouses, we want to replicate the result based on certain activity. For example, for warehouse W2 acitivity C, we want to replicate the result for item count, item used, usage % by using warehouse W2 activity A. It might have other combination like warehouse W3 activity C to replicate result with warehouse W3 activity B. How to create this measure? 🙂

[Ashish_Mathur]

I am not sure.  To start with, create a 2 column table of warehouse and activity.  For each warehouse, show the activity you would like to refer to incase there is no activity data of that warehouse in your fact table.

[bhanu_gautam]

@PBI_newuser , First create a usage measure

   Usage % = DIVIDE(SUM('Table'[Item Used]), SUM('Table'[Item Count]), 0)

Then you can create a calculated column that assigns the Usage % of Activity A to Activity C in Warehouse W2.

Adjusted Usage % =
IF(
'Table'[Activity] = "C" && 'Table'[Warehouse] = "W2",
CALCULATE(
[Usage %],
'Table'[Activity] = "A",
'Table'[Warehouse] = "W2"
),
[Usage %]
)