Forum Discussion

D3K's avatar
D3K
Advocate II
7 years ago
Solved

Using a few selections while modelling

Hello everyone!   Will appreciate any help if it's even possible to solve the question   I'm trying to make a model, which will show, how would change the final cost of a dish after changing of t...
Need Help
LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

Hi D3K

 

Please check results of my file:

 

copy paste the below in your browser:

https://app.powerbi.com/view?r=eyJrIjoiNmZkNThkNzUtYWI0YS00MWIxLTllZjktODk1YmY3MTIxZWM5IiwidCI6IjRiMmQxZTZhLWZlZmMtNDFmMi04OTZiLWFkNWFjNTk0ZWJlOSIsImMiOjF9

 

i will upload the pbix in a couple of hours

D3K's avatar
D3K
Advocate II
7 years ago

LivioLanzo

 

Firstly, I want to say, that your report looks great, even better than I've constructed in my mind as the final result! 

 

And it works almost perfect:

- costs for chosen ingredients changes for all other dishes

- and the cost of burger is also calculating correctly

 

Thanks!

 

But check it out to got what I'm trying to explain. Imagine that supplier increase price only for chicken, and the price for beef wasn't change. And we need to increase only cost for chicken and don't touch cost of the rest meat ingredients. And after that we'll see how is changing cost of burgers with chicken.

 

That's why I've separated ingredients for blocks: choose Chicken from Meat block and increase, choose Onion from Green block and change the cost. 

And I saw the possibility that when any block is filtered and one of it's ingredients is selected - to put changed value into the cost model column. But my knowledge in DAX is not enough to make it, that's why I'm here

 

Anyway I'm really impressed with the current result it's awesome :)

    • D3K's avatar
      D3K
      Advocate II
      7 years ago

      LivioLanzo

       

      It's absolutely AWESOME! I can't believe!

      My knowledges definately wasn't enough for that system :)

       

      THANK YOU so much! Great!

    • D3K's avatar
      D3K
      Advocate II
      7 years ago

      LivioLanzo

       

      Want to thank you one more time for even noted hints in DAX formulas :smileyhappy:

      • LivioLanzo's avatar
        LivioLanzo
        Solution Sage
        7 years ago

        No problem! Actually you can use this formula as well as it is more optimized and runs a bit faster:

         

         

        Cost of Burger =
        VAR IsIngredientFiltered =
            ISFILTERED ( ApplyPercentageIngredients[Ingredient Name] )
        RETURN
            SUMX (
                BurgersProducts,
                -- get ingredient cost
                VAR IngredientCost =
                    RELATED ( Ingredients[Cost] )
                VAR PercToApply =
                    IF (
                        IsIngredientFiltered,
                        -- check if any ingredient is filtered
                        VAR GroupName =
                            RELATED ( Ingredients[Group Name] ) -- get group name
                        VAR IngredientName =
                            RELATED ( Ingredients[Ingredient Name] ) -- get ingredient name
                        RETURN
                            IF (
                                -- test is current ingredient is included in filter
                                CONTAINS (
                                    VALUES ( ApplyPercentageIngredients[Ingredient Name] ),
                                    ApplyPercentageIngredients[Ingredient Name], IngredientName
                                ),
                                SWITCH (
                                    GroupName,
                                    "Green", [% Green Cost Change Value],
                                    "Meat", [% Meat Cost Change Value],
                                    "Bread", [% Bread Cost Change Value],
                                    "Sauce", [% Sauce Cost Change Value],
                                    "Other", [% Other Cost Change Value],
                                    0
                                ),
                                0
                            ),
                        0
                    )
                RETURN
                    [Quantity of ingredients] * IngredientCost
                        * ( 1 + PercToApply )
            )