Using a measure to get the right values in the right column

[olondo]

Dear Community,

Recently I create a dimension Bucketgroups in the datawarehouse to create a view of the slow moving stock.

This dimension, I can use to move the Excess in the right column. The problem is that I need a relationship based on the result of a measure. Maybe there is something possible in PowerBI (because there is always something possible in PowerBI 😉 ), but I don't know what the solution is. Hopefully you can help me with this challenge.

The dimension bucketgroup shows the following table:

And I have a measure named Excess created in PowerBI:

(Quantity item - Min. Stock) / SoldQuantitylastyear * 360.

For an example, I have an item with result 1097,14 (measure Excess). This value do I need to put the following measure in category 3-4 jaar.

Measure = quantity item * soldquantitylastyear (formula is a quite harder, will spare you on that). --> result 50

Sow the result for this item needs to be:

So, the measure where the result is 50 needs to be in category 3/4 jaar. To calculate which category the measure needs to be is the result of the measure Excess.

Hopefully I have explained the hardness of this issue and there is something possible in DAX or PowerBI.

Kind regards, Tim

[v-juanli-msft]

Hi @olondo 

The measure Excess: (Quantity item - Min. Stock) / SoldQuantitylastyear * 360,

Is any part of the measure a column or a measure?

Can i create a caluclated column in stead of a measure?

Do you have any slicer to change the measures?

Any way, There are some useful references below:

https://www.daxpatterns.com/dynamic-segmentation/

https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[olondo]

Dear @v-juanli-msft 

To answer your questions:

Is any part of the measure a column or a measure?

it is all based on measures

Can i create a caluclated column in stead of a measure?

I have tried to created a calculated measure but there isn't enough memory to complete the column unfortunately

Do you have any slicer to change the measures?

No, in this report we don't use any slicers. It is a report focused on all inventorytransactions based on all years. 

Hopefully I have helped you and other colleagues on this community to help me further.

Wil look and investigate your links next weekend.

Thanks in advantage, Tim