Best Practice Question

[VulcanPromance]

Hi.. I struggle alot with creating a max variable I can use for calculations and slicing.

The idea is to show the yearly price for each Bundle where you only pay for the highest number of License (using that license price) within the bundle. When I click on a country (slicing) it should only show the max for that country.

Later in the model I will use the yearly cost to calculate shared costs that are  percentage based on country yearly cost vs total yearly cost. 

I tried countless of ways of doing this. But I cant seem to get a consistent variable to use. I calculated max both as columns in the price table and as a measure to use. But somewhere down the line something is allways messed up, and the culprit seems to be the way I calculated the max value which dont work in other calculations.

So.. is it best to use calculated columns to find the max, or calculated measure? Could someone please show me the proper way?
https://1drv.ms/u/s!AsOQeIMpZu5M9lgSKWorfDCabCrZ?e=3MMB1K

[MFelix · Accepted solution]

Hi @VulcanPromance ,

To what I can understand here you issue is based on the calculation of the percentage of Yearly costs per country this can be achieve by making the following measures:

Monthly Price = 
VAR temp_Table =
    SUMMARIZE (
        'Price Table';
        'Price Table'[Bundle Name];
        'Price Table'[Price];
        "Users"; DISTINCTCOUNT ( Licenses[UserID] )
    )
RETURN
    SUMX ( temp_Table; 'Price Table'[Price] * [Users] )

Yearly Price = [Monthly Price] * 12

I'm assuming the Yearly is just multiplyig by 12 months.

Then you need to calculate the percentage of costs per country selected:

% per Country = [Yearly Price] / CALCULATE([Yearly Price];all(Employees[Country]))

If you have a country table that should be based on that table and not employees table.

Now just make the measures for the Shared Costs:

Shared_Costs = SUM('Cost Table'[Cost]) // Just used as a auxiliary calculation

Shared_Costs_Selected_Countries = [% per Country] * [Shared_Costs]

Markup 5% = [Shared_Costs_Selected_Countries] * 0,05

Overhead 10% = [Shared_Costs_Selected_Countries] * 0,1

Fee 3% = [Shared_Costs_Selected_Countries] * 0,03

BO4 Reporting 12% = [Shared_Costs_Selected_Countries] * 0,12

Total Shared Costs = [Shared_Costs_Selected_Countries]+Measure_Table[Markup 5%]+[Overhead 10%]+[Fee 3%]+[BO4 Reporting 12%]

This values will give you the calculation for the shared costs now you only need to sum the value of the last measure with the Yearly costs and you are all set:

Total Costs Per country = [Yearly Price] + [Total Shared Costs]

Check the image below and the PBIX file attach.

Any questions please tell me.

[v-chuncz-msft]

@VulcanPromance 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Sum-of-values-in-a-measure-with-divide-measure/m-p/296768#M...

[VulcanPromance]

actually this didnt solve my question.. it autosolved it it seems.  @MFelix  do you have more insight?

[MFelix · Accepted solution]

Hi @VulcanPromance ,

To what I can understand here you issue is based on the calculation of the percentage of Yearly costs per country this can be achieve by making the following measures:

Monthly Price = 
VAR temp_Table =
    SUMMARIZE (
        'Price Table';
        'Price Table'[Bundle Name];
        'Price Table'[Price];
        "Users"; DISTINCTCOUNT ( Licenses[UserID] )
    )
RETURN
    SUMX ( temp_Table; 'Price Table'[Price] * [Users] )

Yearly Price = [Monthly Price] * 12

I'm assuming the Yearly is just multiplyig by 12 months.

Then you need to calculate the percentage of costs per country selected:

% per Country = [Yearly Price] / CALCULATE([Yearly Price];all(Employees[Country]))

If you have a country table that should be based on that table and not employees table.

Now just make the measures for the Shared Costs:

Shared_Costs = SUM('Cost Table'[Cost]) // Just used as a auxiliary calculation

Shared_Costs_Selected_Countries = [% per Country] * [Shared_Costs]

Markup 5% = [Shared_Costs_Selected_Countries] * 0,05

Overhead 10% = [Shared_Costs_Selected_Countries] * 0,1

Fee 3% = [Shared_Costs_Selected_Countries] * 0,03

BO4 Reporting 12% = [Shared_Costs_Selected_Countries] * 0,12

Total Shared Costs = [Shared_Costs_Selected_Countries]+Measure_Table[Markup 5%]+[Overhead 10%]+[Fee 3%]+[BO4 Reporting 12%]

This values will give you the calculation for the shared costs now you only need to sum the value of the last measure with the Yearly costs and you are all set:

Total Costs Per country = [Yearly Price] + [Total Shared Costs]

Check the image below and the PBIX file attach.

Any questions please tell me.

[VulcanPromance]

@MFelix I have a followup question to this scenario. One of my countries are pakistan. They deduct some taxes so I have to add another 12 %  as a shared cost, but only for pakistan. The other countries do not deduct this tax, so this new column would be blank if I choose another country on the slicer.

I was thinking I would Create a variable
WH TAX 12% = IF(employees[country]="Pakistan",Shared_Costs_Selected_Countries]*0.12,0)

Something like that..

Then list that variable along with the other shared cost..

Is there a sleeker way to do this though? Like adding a new Visual, that only contains information if you choose Pakistan on the slider? and adds that value to the Total cost pr selected country.

I'm reaching out to you because you solved the other parts so efficiently, and you already have the template files in this forum post.

[VulcanPromance]

@MFelix 

I solved most of it myself. I created the IF variable 

WH Tax 12% = IF(max(Employees[Country])="Pakistan",[Shared_Costs_Selected_Countries]*0.12,0)

And this by itself works like a charm. However, I have a total here.. which when using my slicer with nothing selected shows 

Total Costs Per selected country = [Yearly Price] + [Total Shared Costs] + [WH Tax 12%] 

I added the + WH Tax 12% so that if I choose Pakistan in my slicer, it will show the total cost including the 12% tax.. as well as a visual that shows the 1737 EURO that is the 12%.

But when I choose nothing, the total is WITHOUT the 12 % but it should also be included in the total cost pr selected country if nothing is selected not only when I choose pakistan

[MFelix]

Hi @VulcanPromance ,

You must use a SUMX to calculate this because when you are using an if statment the measure will check if there is pakistan selected and make the calculation of the 12% for all the lines.

Should be something similar to:

WH Tax 12% = SUMX(Table;[Shared_Costs_Selected_Countries] * IF(max(Employees[Country])="Pakistan",0.12,0))

Rename the Table by the one in your model.

[VulcanPromance]

Hi again @MFelix . Thank you for your patience 🙂

Using this

WH Tax 12% = SUMX('Cost Table',[Shared_Costs_Selected_Countries] * IF(max(Employees[Country])="Pakistan",0.12,0))

Does the exact same thing.

Here is what I have now
With Pakistan selected

Without anything selected you notice the 12% from pakistan is gone, but i need it added to the WH Tax 12% column or at a minimum at the Total Cost Pr Selected and WH Tax 12% visual.. Is it even possible?

[MFelix]

Hi  @VulcanPromance ,

Instead of the MAX function try the Selectedvalue:

WH Tax 12% = SUMX('Cost Table',[Shared_Costs_Selected_Countries] * IF(SELECTEDVALUE(Employees[Country])="Pakistan",0.12,0))

[VulcanPromance]

@MFelix 

Although SelectedValue makes more sense in the measure, it works like the other version. I'm not sure if I can use these variables.

The Total Cost Pr Selected Country variable is ment to show total cost for the country I choose in the slicer. The If statement adds another cost if I choose a specific country (pakistan). Then suddenly I want to add that pakistan cost to the total cost pr selected country without selecting it.

The solution would be if the IF statement somehow triggered if Pakistan was selected, OR if NOTHING was selected. Yet when Nothing is selected the calculation can only calculate the 12% for the pakistan cost, not the cost for all countries combined.

I probably need to create separate variables for it and skip it in the shared overview...

[MFelix]

Hi @VulcanPromance ,

Instead of the cost table try to make the SUMX over the EMployees table.

IF this doesn't work if you can share a file would be great.

[VulcanPromance]

Thats pretty sweet. But the original issue is the count of userID's.. as you can see on facility management it counts 9 unique users. And thats the correct amount of users. However, the cost for the licenses should be based on the highest amount of users with in each Bundle.. 

Example

Facility managment has 2 license types.. 

Workorders - 8 users

Helpdesk Backoffice - 3 users

a user can have both or only 1 license of the above. So the unique users are 9, but the cost should be based on the license type with most users i.e 8...

And thats where I begin to struggle, cause using normal measures to calculate the cost (using various of ways to find the max value) seems to fail. Hence the question, what is the best practice to calculate max and get it to work in the calculations you've used above.. 🙂

I've purchased a guide to DAX book now, so that I can read more up on the language.. but would appreciate a solution on this from an expert like yourself.. 🙂

[MFelix · Accepted solution]

Hi @VulcanPromance ,

Add the following measure to your calculation:

Count of users =
CALCULATE (
    MAXX (
        SUMMARIZE (
            'Price Table';
            'Price Table'[Bundle Name];
            'Price Table'[License Name];
            "@User_Count"; DISTINCTCOUNT ( Licenses[UserID] )
        );
        [@User_Count]
    );
    ALLEXCEPT ( 'Price Table'; 'Price Table'[Bundle Name] )
)

Now just need to redo the Montlhy price and everything else will calculate accordingly:

Monthly Price = 
VAR temp_Table =
    SUMMARIZE (
        'Price Table';
        'Price Table'[Bundle Name];
        'Price Table'[Price];
        "Users"; [Count of users]
    )
RETURN
    SUMX ( temp_Table;'Price Table'[Price] * [Users])

Just added the Count of Users in your measure. There is a small difference but believe is rounding.

Check result attach.

[MFelix]

Hi @VulcanPromance ,

Not really sure what you are looking for.

Can you please specify with examples what you need in terms of:

• Yearly price for each Bundle where you only pay for the highest number of License (using that license price)
• Click on a country (slicing) it should only show the max for that country
• Later in the model I will use the yearly cost to calculate shared costs
• Percentage based on country yearly cost vs total yearly cost

Not really sure about what is the number you need for the calculations,is it based on the value times number of users? what is the result basically.

[VulcanPromance]

Yeah.. this is basically the result I want

And when I click on Norway (slicer)

I get these numbers instead

and I need to be able to drill down on license names in the license visual..