Quick, easy and urgent: DAX division of variable numerator by fixed denominator

[jonnyko]

Hi everyone!

Table 1:

Site	Theme	Score
London	A	2
London	A	3
London	A	2
London	B	4
Paris	A	3
Paris	A	4
Paris	A	1
Paris	B	1

Table 2: Dim_ThemeMax

Theme	Max
A	15
B	6

How do I create something in DAX to plot each site's score per theme as a % of theme max score, rather than just plot the raw scores per theme?

Many thanks!!

[Jihwan_Kim · Accepted solution]

Hi, @jonnyko 

I am not sure how your desired outcome looks like, but please check the link down below.

score percentage =

SUMX('Table', 'Table'[Score]/RELATED(ThemeMax[Max]))

https://www.dropbox.com/s/12ixbhob23nstx1/jonnyko.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[jonnyko]

thanks for the quick response Jihwan, it works on your file and data structure is identical to mine, but when I put it into my Power BI I think the denominator isn't quite pulling through the value per theme. It seems maybe that it's just a fixed value of the count of number of themes there are - there is a 1:many relationship between my Dim_theme table and table with the individual scores though based on Theme....

any thoughts?

[Jihwan_Kim]

Hi, @jonnyko 

Thank you for your feedback.

Please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thanks.

[jonnyko]

just realised I went wrong by adding a sum( to the table score within my sum( already - your solution has worked. Thank you Jihwan!