How to ventilate cost between different line ?

[fabd]

Hello Community.

I hope you will understand my need. English is not my native language and even in France, it's difficult to explain what i would like to do in PowerBI.

So, let's start by the beginning. I'm an IT guy in charge of AZURE service in my company.

We have deployed an application that is used my many service. We would like to charge back each service to what they consume in Azure.

To do this, we put on all ressource group a tag which contain the service.

We have the following table :

Service	Cost
IT/System	100
IT/Network	200
IT/DB	300
IT All	400

"IT All" is a cost supported by all teams. I would like to split this cost to all service in proportion of there consommation.

So, for example, IT/DB should pay 50% of the 400 of IT All

How i could do this using PowerBI ?

The cost is calculated each day

Any idea, help would be appreciated. I know how to do in Excel, but in DAX, i'm totally noob

Thanks 🙂

[andres777 · Accepted solution]

There are several ways to achieve this,   Assuming 400 is the cost for all Segments....

• You can create a new column on your table with the calculated values, + sort column
• Or you can create a calculated column using DAX (as you requested)
• First create a new calculated column by right-click into your table and select "New column"
• Then rename your column to what you want (I chose Percent = )

Type the DAX formula : 

Percent = 
    FORMAT(DIVIDE('Table'[Cost], MAX('Table'[Cost])), "##0 %")

I added the percent format, you can change it to "##0.0# %" if you want decimals

• Add a new tale into your Report, and use the columns Service, Cost , Percent, sort
• I renamed sort to . (to minimize space and hide better), sort by sort column, and hide

The values should show up formatted correctly as in the pictures:

After you add the calculated column, your table will show the Percent values...

After hiding the sort column and formatting in Power BI, your table should look like this

If 400 is the cost we need to share among other segments, the calculation is different... let me update this next.....

Correction :

The formula to prorate each value and distribute the "IT ALL" cost value weighted with the rest of the areas is :

Prorated = 
    var ITCost = LOOKUPVALUE('Table'[Cost], 'Table'[Service], "IT ALL")
    var TotCost = SUM('Table'[Cost]) - ITCost
    RETURN 
        IF ('Table'[Service] <> "IT ALL",
            FORMAT(  DIVIDE('Table'[Cost], TotCost)  , "##0 %"), 
            BLANK()
        )

Prorated = % of distribution among each service (in text format to display %)

Add the following formula to calculate the prorated values :

Weighted Cost = 
    var ITCost = LOOKUPVALUE('Table'[Cost], 'Table'[Service], "IT ALL")
    var TotCost = SUM('Table'[Cost]) - ITCost
    RETURN 
        IF ('Table'[Service] <> "IT ALL",
            DIVIDE('Table'[Cost], TotCost) * ITCost, 
            'Table'[Cost]
        )

Weighted Cost = Cost based on Prorated %

I hope this helps,  et bonne chance

Andres 🙂

[jthomson · Accepted solution]

So if I'm reading this right, you want System, Network and DB to be allocated one sixth, one third and half of the All costs respectively? Try this measure:

SplitAllByDept =

var totalcost = CALCULATE(sum('Table'[Column2]),all('Table'[Column1]))

var allcost = CALCULATE(sum('Table'[Column2]),'Table'[Column1]="All")

var proportion = divide(sum('Table'[Column2]),totalcost-allcost)

return sum('Table'[Column2])+proportion*allcost

It gives a bit of a funky result for the all column but you can work that out with an if column1 = all, 0, then what's being returned

[fabd]

Thanks you too Jthomson for your help.

I will try this too and tell you if i successed

🙂

[fabd]

Thanks guys for your help.

Both solutions works for me 🙂

[andres777 · Accepted solution]

There are several ways to achieve this,   Assuming 400 is the cost for all Segments....

• You can create a new column on your table with the calculated values, + sort column
• Or you can create a calculated column using DAX (as you requested)
• First create a new calculated column by right-click into your table and select "New column"
• Then rename your column to what you want (I chose Percent = )

Type the DAX formula : 

Percent = 
    FORMAT(DIVIDE('Table'[Cost], MAX('Table'[Cost])), "##0 %")

I added the percent format, you can change it to "##0.0# %" if you want decimals

• Add a new tale into your Report, and use the columns Service, Cost , Percent, sort
• I renamed sort to . (to minimize space and hide better), sort by sort column, and hide

The values should show up formatted correctly as in the pictures:

After you add the calculated column, your table will show the Percent values...

After hiding the sort column and formatting in Power BI, your table should look like this

If 400 is the cost we need to share among other segments, the calculation is different... let me update this next.....

Correction :

The formula to prorate each value and distribute the "IT ALL" cost value weighted with the rest of the areas is :

Prorated = 
    var ITCost = LOOKUPVALUE('Table'[Cost], 'Table'[Service], "IT ALL")
    var TotCost = SUM('Table'[Cost]) - ITCost
    RETURN 
        IF ('Table'[Service] <> "IT ALL",
            FORMAT(  DIVIDE('Table'[Cost], TotCost)  , "##0 %"), 
            BLANK()
        )

Prorated = % of distribution among each service (in text format to display %)

Add the following formula to calculate the prorated values :

Weighted Cost = 
    var ITCost = LOOKUPVALUE('Table'[Cost], 'Table'[Service], "IT ALL")
    var TotCost = SUM('Table'[Cost]) - ITCost
    RETURN 
        IF ('Table'[Service] <> "IT ALL",
            DIVIDE('Table'[Cost], TotCost) * ITCost, 
            'Table'[Cost]
        )

Weighted Cost = Cost based on Prorated %

I hope this helps,  et bonne chance

Andres 🙂

[fabd]

Hi Andres,

Thanks for you mini tutorial. I'm gonna try this right now !!!