Solved: Re: Using a few selections while modelling

D3K · ‎10-10-2018

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 the cost of some of it's ingredients.

1. I have a list of dishes (burgers), each of those burgers is consist of some ingredients, sometimes ingredients are unique, sometimes - not. Cost of the burger is calculated as a sum of costs of it's ingredients.

2. Also I have a list, where shown, which and how many ingredients are in particular burger.

3. And one more list of all ingredients with it's cost for 1 piece

The problem is that I need to have an opportunity to change cost for a several ingredients separately at one time. For example, for onion +10%, for bread +3% and so on.

To be able to choose different ingredients I've made a few supported blocks: green, meat, bread, sauce, other. And one supported table of all ingredients together, called "Filter Cost of ingredients". There no relations between them.

As I see the further process, I need to choose some ingredients in each block and change the cost via What-If parameter. After that need to get changed cost of particular ingredient (cost for which was changed) into the full table of ingredients (step 1 on screenshot). And if ingredient is not selected and cost of it is not changed, so in this column we need to note it's standart cost. So after step 1 we have a table with one column (measure) with changed costs and standart costs.

At step 2 I will compare Fact costs with Model costs. And at step 3 I will collect Fact and Model cost of every burger and will be able to compare it.

So, the main problem is how to achieve step 1 is working. I don't even know is it possible and will be glad to any thoughts and help.

Here is the link to .pbix file

https://www.dropbox.com/s/kubatkmky6kh7di/Share_Model_2.pbix?dl=0

Thanks!

LivioLanzo · ‎10-10-2018

Hi @D3K

you can download the pbix file here: https://1drv.ms/u/s!AiiWkkwHZChHjx6QGS8z8QHzSMGu

also if you click on the link in my first post you can check online the new report

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

View solution in original post

LivioLanzo · ‎10-10-2018

Hi @D3K

please see below my results and check if they are correct.

I will be able to share the pbix with you in 1 hour

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

D3K · ‎10-10-2018

Hi @LivioLanzo

Got you! I hope it's correct on the screenshot 🙂

But want to pay your attention, that the main problem is that it's needed to be able choose 1 or 2 from the group. For example, change the cost only for Tomato from group Green.

Anyway, thanks a lot for your trying to help!

LivioLanzo · ‎10-10-2018

hI @D3K

at the moment what I have is that when you change the % change of Green Ingredients, the % change is applied to the price of all the ingredients marked as green? am I correct?

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

D3K · ‎10-10-2018

@LivioLanzo

As the first step to the goal, I suppose, it's correct. But the main target is to be able to choose and change only Tomato in block Green and not to change Onion in the same block, for example.

Please feel free to ask if I've explained it easy to got.

LivioLanzo · ‎10-10-2018

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

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

D3K · ‎10-10-2018

@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 🙂

LivioLanzo · ‎10-10-2018

Hi @D3K

you can download the pbix file here: https://1drv.ms/u/s!AiiWkkwHZChHjx6QGS8z8QHzSMGu

also if you click on the link in my first post you can check online the new report

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

D3K · ‎10-10-2018

@LivioLanzo

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

LivioLanzo · ‎10-10-2018

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 )
    )