How to distribute the cost based on percentage

[zzzsharepoint]

Hi, I have below table which is like this:

Cluster	Cost
A	76
B	71
A	71
A	91
C	10

and have another table which has many-many to relationship with above table

Cluster	Project	Value
A	XYX	0.8
A	XYZ	23.7
B	POI	86
A	LUB	72.9

I would like to see a visual which maps the cost distribution of each project in a cluster. So like in a Slicer If I Select Cluster A. I should get the result of all Projects in Cluster A and their cost. The cost will be ( Total Cost of Cluster A * Value of that project).

So in example above you have Cluster "A" whose total cost is (71+76+91) 238 and projects in cluster A like XYX has cost which is 238*(0.8% of Total value of all projects in Cluster A) i.e. (0.8*100)/(0.8+23.7+72.9) and other project like XYZ has cost as (238*24.3) here 24.3 I got by 24.3*100/(0.8+23.7+72.9).

How Can I do this for other projects in other cluster and show it in Visual based on the cluster selected in the slicer.?

[v-yadongf-msft · Accepted solution]

Hi @zzzsharepoint ,

Please try following measures:

Cluster_cost = CALCULATE(SUM('Table1'[Cost]),FILTER(ALL('Table1'),'Table1'[Cluster] = SELECTEDVALUE('Table1'[Cluster]))) 

Value of all projects = 
var sum_value = CALCULATE(SUM('Table2'[Value]),FILTER(ALL('Table2'),'Table2'[Cluster] = SELECTEDVALUE('Table2'[Cluster])))
var current_project = SELECTEDVALUE('Table2'[Value])
return
DIVIDE(current_project*100, sum_value)

Result = [Cluster_cost] * [Value of all projects]

The result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[djurecic]

HI @zzzsharepoint ,

 You might want to try to summarize the first table to get one cost value per cluster, then create a one to many relationship between that table and the second table.

Something like this- adjust for your table and field names

New table= SUMMARIZE(FirstTableName, FirstTableName[Cluster], "Cost",SUM(FirstTableName[Cost]))

https://learn.microsoft.com/en-us/dax/summarize-function-dax