Calculate filtered by measure

[Harry1980]

Hi all,

I need to create a measure in PBI desktop which shall calculate the available excess stock on material item level, but only for our main warehouse (i.e. we have several warehouses, but only one is the main warehouse). However, the main warehouse indicator is not something which exists already in our data model. Hence, I have created it through a measure. Then I want to use that warehouse indicator measure  in another measure which should return the available excess stock in a our main warehouse. I understand that filtering with a measure is challenging. However, I cannot create e.g. a calculated column as  I do not have any access to the data model. So far the outcome is not as expected. I.e. the 2nd measure below does not show me the excess stock in the correspondent main warehouse. For me the output does not make sense at all

My measure for identifying the main warehouse:

MW ind = if(left(calculate(max(dimmaterialplantspecific[specialprocurementtypeid])),1)<>"Z","Main","Satelite")

My measure to get the excess stock in the main warehouse - which returns wrong/odd results:

Excess Main= CALCULATE (

SUM ( dimMaterialPlantSpecific[Excess] ),

FILTER ('#ReportMeasures', '#ReportMeasures'[MW ind] = "Main" )

)

Do you see there any way to solve my problem? Or in other words what is wrong in my measure?

Your support is highly appreciated.

Cheers Harry

[v-yalanwu-msft]

Hi, @Harry1980

I tested it ,you only need to modify the measure about [Excess Main] :

In my simple test, you could compare the difference about two table.

1.Reportmeasures table's measure.

Excess Main = CALCULATE ( SUM ( [Excess] ), FILTER ('#ReportMeasures', [MW ind] = "Main" ))

 2.'dimMaterialPlantSpecific' table's measure.

Excess Main2 = CALCULATE ( SUM ( [Excess] ), FILTER ('dimMaterialPlantSpecific', [MW ind] = "Main" ))

 The final output is shown below:

Best Regards,

Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[Harry1980]

Hi, sorry that I forgot to share some data:  The file is also attached. 

In this example below I have a material ABC which we have stocked in 4 different warehouses ("Plant"). However, only one warehouse is to be regarded as the main warehouse. Please check in the in first table from above the measure "MW ind". In this example here it is "Plant" 4.

Then in the second table I have again material "ABC" and here I would to show only the excess stock which is maintained for our "Main" warehouse. However, as you can I get odd results. I should have gotten 0, but PBI returns 5000. Does not make sense.

Please note that I am fully aware of that a calculated column would be the easiest. But our IT does not have the ressources at the moment. In addition when to tried to bypass it by creating a composite model I got a run time due to missing processing power.

Do you see any smart way for a workaround?

[ryan_mayu · Accepted solution]

@Harry1980 

pls see the attachment to check if this is what you want.

[Harry1980]

Thank you very much. This works perfectly. The support is highly appreciated.!!!!

[ryan_mayu]

you are welcome