Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
zzzsharepoint
Helper I
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
v-yadongf-msft
Community Support
Community Support

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:

vyadongfmsft_0-1669973278894.png

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.

View solution in original post

2 REPLIES 2
v-yadongf-msft
Community Support
Community Support

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:

vyadongfmsft_0-1669973278894.png

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.

djurecicK2
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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