Measure to calculate portion of total value

• Question

• Scenario: we have a table with "projects", where every project has a total sum of \$\$\$. We also have a table that defines competencies within project, where every competency might have some percentage value, totaling to 100% within a single project, e.g.:

ProjectKey | CompetencyKey | CompetencyRatio
-----
Project1 | Competency1 | 90
Project1 | Competency2 | 10
ProjectX | Competency2 | 100

I want to define a measure to measure amount of \$\$\$ driven by each competency, based on project's \$\$\$ and competency ratios. The tables are already linked through a project key. Problem is, when I go to "define quick measure" and select "Mathematical - multiply", the system creates a measure that multiplies SUM (CompetencyRatio) by SUM (Project\$\$\$), which is of course incorrect, and it is impossible to define a measure which would simply multiply one by another.

What is wrong here?

Thursday, November 7, 2019 3:02 PM

• What about adding first a new column to the competency-table with this formula:

\$\$\$ = (Competency[Percentage]/100)*RELATED(Projects[\$\$\$])

MeasCompetency = sum(Competency[\$\$\$])

Query it

Thursday, November 7, 2019 5:30 PM

All replies

• Hey... so are you talking about power pivot?

Query it

Thursday, November 7, 2019 4:44 PM
• I am talking about Measures in Power BI model
Thursday, November 7, 2019 5:02 PM
• What about adding first a new column to the competency-table with this formula:

\$\$\$ = (Competency[Percentage]/100)*RELATED(Projects[\$\$\$])