DAX - return a result based on the result of a MINX in the same measure

[I_LOVE_POWER_BI]

Hi community,

Sorry for the terrible "subject", but I can't come up with a better title.

Background: I want to generate a benchmark simulation. In this scenario I produce a product, which consists of many different materials. The very same product is produced in different workshops. Every workshop uses a slightly different %-split to define the concrete amount of material usage. Also each workshop purchases those materials with different prices from local suppliers.

Now, in my benchmark, I want to find out which workshop produces the product the cheapest, based on the local prices for each material multiplied with the material %-split in the product. The workshop that produces the product the cheapest then becomes the benchmark for the other workshops based on the material %-split used.

....hope you are still with me. 😀

Now what I do in order to get there: 

1. First I need to find out which workshop produces the product the cheapest
2. In order to then use that very material split used in the cheapest product to simulate it with product prices for each material in other workshops using the cheapest Material-Split-in-%

The Product in "Workship B" is being produced the cheapest. Then I want to use that split to simulate the effect in the other workshops.

First step I can manage: I define point 1 using CALCULATE and MINX to calculate the cheapest Product Price in the given filter context:

Cheapest Product Price =
CALCULATE (
    MINX (
        SUMMARIZE (
            'Recipes',
            'Recipes'[Workshop-Product-Key],
            'Recipes'[Material],
            "Compound-Material-Price", 

            SUM ( 'Recipes'[%-Split] ) * [Price]
        ),
        [Compound-Material-Price]
    ),
    REMOVEFILTERS ( 'Workshop' )
)

So, I guess I have 80% of the job done. But my struggle now is, that the result of the above measure gives me the price, not the %-split. That's fine at first sight, because I have to define the minimum first, in order to define which split to use. Now it comes:

How can I get the %-Split of the cheapest price from the first measure as an output?   

I can't think of a suitable filter, boolean or condition to tell "it" to use the split based on the cheapest price...

Hope you understood my problem.

Thanks for taking the time!!!

[Anonymous]

Out of curiosity, I tried it. The following is the output.

I used the following sample data model.

I am not posting the DAX code deliberately because my DAX code works partly because of these table structures and the relationship between them. Further, I have used multiple CALCULATE() functions to do transitions between row-contexts to filter-contexts within Measures.

Refer to the screenshots and if you are trying to do something similar, then post your table structures and the relationships between them. If possible, send your pbix with some sample data in it. I will fill in the DAX and post them back.

[I_LOVE_POWER_BI]

Thanks so much for taking the time, that's very much appreciated, awesome!

My tables look slightly different. Sadly I didn't find a way yet to share my data, since our organization disabled OneDrive share with people outside the org. :-(((

My tables look like this and a connected as follows (feels like a big loss in lifetime to reproduce this in excel, lol):

So the only difference to your model is, that "Material-Split" and "Product" are the same in my data model.

Btw. I am really amazed how good you got the point out of my story, really awesome and I am really thankful for what you did!

Looking forward to your code & answer based on my data model adaption very much, thanks again!!!

Cheers!

[Anonymous · Accepted solution]

I used the same sample data, table names and field names shown in your image, and created relationships between them.

I have used Materials[Material] and Workshops[Workshop] on all Matrix Visuals.

All values are measures. 

The following are the DAX used for the measures.

Split% = SUMX(Products,Products[Split])

Prices = SUMX(Prices,Prices[Price])

Product Cost =
VAR MaterialsInCurrentRowContext =
    VALUES ( Materials[Material] )
VAR AddCost =
    ADDCOLUMNS ( MaterialsInCurrentRowContext, "Cost", [Prices] * [Split%] )
VAR Result =
    SUMX ( AddCost, [Cost] )
RETURN
    Result

Minimum Product Cost =
VAR AllWorkshops =
    ALLSELECTED ( Workshops[Workshop] )
VAR AllProducts =
    ALLSELECTED ( Products[Product] )
VAR CJ =
    CROSSJOIN ( AllWorkshops, AllProducts )
VAR AddCostColumn =
    ADDCOLUMNS ( CJ, "Product Cost", [Product Cost] )
VAR Result =
    MINX ( AddCostColumn, [Product Cost] )
RETURN
    Result

Minimum Cost Workshop =
VAR AllWorkshops =
    ALLSELECTED ( Workshops[Workshop] )
VAR AllProducts =
    ALLSELECTED ( Products[Product] )
VAR CJ =
    CROSSJOIN ( AllWorkshops, AllProducts )
VAR AddCostColumn =
    ADDCOLUMNS ( CJ, "Product Cost", [Product Cost] )
VAR MinCost =
    MINX ( AddCostColumn, [Product Cost] )
VAR MinCostRecord =
    FILTER ( AddCostColumn, [Product Cost] = MinCost )
VAR Result =
    CONCATENATEX ( MinCostRecord, Workshops[Workshop] )
RETURN
    Result

Simulated Product Cost =
VAR MaterialsInCurrentRowContext =
    VALUES ( Materials[Material] )
VAR MinCostWorkshop = [Minimum Cost Workshop]
VAR AddCost =
    ADDCOLUMNS (
        MaterialsInCurrentRowContext,
        "Cost",
            [Prices]
                * CALCULATE ( [Split%], Workshops[Workshop] = MinCostWorkshop )
    )
VAR Result =
    SUMX ( AddCost, [Cost] )
RETURN
    Result

There is one caveat in this solution. I have assumed that there will always be "only one workshop" with minimum cost. If there are two workshops with the same cost, but use different material splits, ???????

What's the probability? 

[I_LOVE_POWER_BI]

Dear screenathv,

Can't tell you how grateful I am! Thanks so much for the support! The measures work very well. I copied everything into my pbix file and it works.

However, may I bother you with one more "specialty"? 

The current behaviour is as follows:

In this view I combined all measures and comparing Workshops B and C. 

My goal is, that based on the cheapest Workshop OVERALL costs, the %-split for each material of that workshop is being applied on the other workshops. However, currently the cheapest material is chosen.

So to be more concrete, for the Workshop C simulation, I want that %-Split from Workshop B is used as a minimum. In other words, the minimum %-Split for each material shall be determined by the total cost minimum.

Also I added a material 6 for testing, which has a price for each Workshop, however is only used in the product split of Workshop C - also in such a case, the simulation should be based on the (cheapest) Workshop B split, so show a blank in the "Simulated Product Cost" column.

What needs to be adjusted to make that possible?

Thanks again a thousand times!!!

[Anonymous]

That's the reason I asked for the pbix. I wanted to understand under what evaluation context the measures are executed. In the matrix screenshot you have posted, you have introduced "material" into the evaluation context of "Minimum Cost". So it was evaluated at the material level. If you don't want that, remove the filters on Materials[Material] using REMOVEFILTERS function.

The two modified measures are given below.

Minimum Cost Workshop =
VAR AllWorkshops =
    ALLSELECTED ( Workshops[Workshop] )
VAR AllProducts =
    ALLSELECTED ( Products[Product] )
VAR CJ =
    CROSSJOIN ( AllWorkshops, AllProducts )
VAR AddCostColumn =
    ADDCOLUMNS (
        CJ,
        "Product Cost", CALCULATE ( [Product Cost], REMOVEFILTERS ( Materials[Material] ) )
    )
VAR MinCost =
    MINX ( AddCostColumn, [Product Cost] )
VAR MinCostRecord =
    FILTER ( AddCostColumn, [Product Cost] = MinCost )
VAR Result =
    CONCATENATEX ( MinCostRecord, Workshops[Workshop] )
RETURN
    Result

Minimum Product Cost =
VAR AllWorkshops =
    ALLSELECTED ( Workshops[Workshop] )
VAR AllProducts =
    ALLSELECTED ( Products[Product] )
VAR CJ =
    CROSSJOIN ( AllWorkshops, AllProducts )
VAR AddCostColumn =
    ADDCOLUMNS (
        CJ,
        "Product Cost", CALCULATE ( [Product Cost], REMOVEFILTERS ( Materials[Material] ) )
    )
VAR Result =
    MINX ( AddCostColumn, [Product Cost] )
RETURN
    Result

This will give you the following output.

Sometimes, even if the measures we write give the correct output in a visual, it could be because of the evaluation context. If one of your users tries the "Analyze in Excel" option from your published report and drag the measure into a Power Pivot and evaluate it under a different context, we cant' be really sure that it will give the correct output. For that, as a report designer, you have to think it through from all angles. 

For example, Given below is the screenshot of two matrix visuals, the first one uses "Materials[Material]" field on the rows, the second visual uses Products[Material] on the rows. See how the "Prices" measure is giving correct results in the first case and wrong prices in the second visual.

At the same time, the "Minimum Product Cost" measure is showing the cost at the material level in one visual and at the product level in another one. Now you have all the measures you need. If required, create multiple variants of the same measure, one with "REMOVEFILTERS" and one without it. And use them in different contexts/visuals accordingly based on how you want the users to see the figures.

[I_LOVE_POWER_BI]

You are right! It works! And yes, you are also right, that the answer REALLY depends on the filter context.

Please don't be angry, but I have another question:

If I have more than a single product in the same matrix visualization, the evaluation shall take place for each product, not over all products:

Is it OK, if I change the "AllProducts" variable from ALLSELECTED to VALUES, so that the filter context on product level is used based on the matrix filter context? Or will this harm something else?

Sorry....feel super nooby here. Thanks again!!!

[Anonymous]

Yes. You could change ALLSELECTED() to VALUES(). But remember to change it both the measures - Minimum Cost Workshop and Minimum Product Cost

[I_LOVE_POWER_BI]

Great! I did that and it works! Thank you sreenathv!

Now testing it, and also try different scenarios again, I realized one (I guess last :-D) problem. In case a Benchmark Workshop (=Minimum) is using a material, that is not used in the other workshops, the total 100% adding up split shall be used to simulated the overall potential:

So in this example, for Material 2 & 6, which are used in the cheapest workshop 2, the simulation does not work, since that material is not used in workshop C here, means it is only calculating with Materials 3, 4 and 6.

Solving this looks very challenging to me...

Do you have another idea here?

Thanks so much!

[Anonymous]

If you want to use Material 2 and 6 for simulation in Workshop C, we could write a DAX code for that. But we are using the pricing from the respective workshop for simulation. Only the split is used from the benchmark workshop. So if 2 materials are not used in Workshop C, obviously, there won't be price available for those materials at Workshop C. If we bring the split% from the benchmark workshop to Workshop C, the price will be empty/blank. What do you want to do in those situations?

[I_LOVE_POWER_BI]

Thanks for the critical feedback. Indeed a good question!

The overall goal of the benchmark is to check, whether the workshops can produce the product cheaper, by adapting to the cheapest material-mix (%-split). The prices for the different materials are maintained for all workshops - whether they currently use the material or not. So yes, calculation shall use the prices of the respective workshop, using the %-split of the cheapest workshop's product. 

[Anonymous]

That was the real-life situation. In your sample data, you could easily fix it as follows...

Instead of maintaining your data as

Add zero lines to it and maintain the data as.

It will fix the problem.

[I_LOVE_POWER_BI]

I see, I can try this, but can't gurantee it in my real life data model - do you have another idea in DAX?

[Anonymous]

In your last screenshot, The simulated product cost of product B at Workshop C is shown as 5.62. Is it correctly calculated or is it wrong? Can you verify it?

[I_LOVE_POWER_BI]

Hi sreenathv,

The total of 5.64 indeed is/was correct. Although the Material 2 & 6 row were empty on row level, but the total was still calculated correctly. Now adding the 0% in the Workshop C's Split%, also on row-level it ends up correctly to the subtotal:

Question now remains to me, whether it is possible to get the result for material 2 & 6, without the 0 in the material-%-split.

Any idea how to do this in DAX without changing the data table?

THANKS!!!

[Anonymous]

I am not giving much of thought to this. But you could try changing the variable MinCostWorkshop in Simulated Product Cost Measure as follows...

VAR MinCostWorkshop = CALCULATE([Minimum Cost Workshop],REMOVEFILTERS(Materials[Material]))

That might fix it, but make sure to test it.