cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## How to distribute the cost based on percentage

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.?

1 ACCEPTED SOLUTION
Community Support

``````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,

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

2 REPLIES 2
Community Support

``````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,

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

Super User

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.

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

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors