How to calculate value for each variable by their percent using DAX Power BI

1
votes

Need your help to solved my problem about DAX Power BI.

So, I'm trying to visualize the total cost for each project that have employee on it using Power BI. but the cost for one employee can be shared to some project by the percentage at every project.

the goal is to calculate the total cost for each employee, then divide it into several project by the percentage.

and here is the excel data. so I have 3 table. COST,MASTER USER, AND PAYMENT ALLOCATION.

-COST

COST

-MASTER USER

MASTER

-PAYMENT ALLOCATION

ALLOCATION

And here is the expected result.

enter image description here

Please help me solved my problem, Thank you so much!

1
powerbidaxpercentagemeasure

1 Answers

0
votes

First, you need to change your COST table a bit:

enter image description here

I replaced "ID" with "Employee Number", because we need COST and ALLOCATION table to have the same Employee keys. I ignored "Product" column because it's not relevant to the question.

Once COST table has the same employee key as ALLOCATION table, you can connect all your tables into the following data model:

enter image description here

Table "User" is connected to tables "Cost" and "Allocation" via "Employee Number". Please note that the connection between "User" and "Allocation" is bi-directional. To change connection from single to bi-directional, double-click on the connection line and change filter direction to BOTH. Now you are ready to write DAX measures.

Measure 1: 

Total Cost = SUM(COST[COST])

This measure simply sums up costs in the table "COST".

Measure 2:

Allocated % = SUM(Allocation[PERCENTAGE])

This measure simply sums up percentages in table "ALLOCATION".

Measure 3:

Allocated Cost = SUMX('User', [Total Cost] * [Allocated %])

If you drop Projects into a matrix visual and add this measure, you will get the following result:

enter image description here

Additional note:

Experienced designers avoid using bi-directional relations in their models (for good reasons). To keep all relations one-directional, modify the last measure as follows:

Allocated Cost = 
CALCULATE(
  SUMX('User', [Total Cost] * [Allocated %]), 
  CROSSFILTER(User[EMPLOYEE NUMBER], Allocation[EMPLOYEE NUMBER], BOTH))

CROSSFILTER does exactly the same thing as bi-directional relation, just programmatically and without unwanted side effects. Such approach will also work in Excel Power Pivot, which does not support bi-directional connections.