How to calculate value per m2 for total lines in a matrix?

[Anonymous]

Dear community,

I seeking for your support for the following:

How could I calculate Budget sales per m2 for total lines in a matrix? Attached you will find a PBIX file with an example. 

Link: https://www.dropbox.com/t/35tPIGgIgqOgCFya 

See for example total lines in red oval.

Example: Gate 1A:

Budget sales €12.000,=

Area in m2: 600m2

Expected budget sales per m2 = 12.000 / 600 = 20 --> however it does show a value of 60

Column 'Budget sales per m2' in materix is a measure

Values per m2 for the individual weeks are calculated correctly.

Thanks in advance for your suggestions!

[v-yalanwu-msft · Accepted solution]

Hi @Anonymous ,

According to your description, you could create two measures (Area and Budget2 ), the following formula to create :

Area = CALCULATE(MAX('Tbl_Data_2'[Area (in m2)]),ALLEXCEPT(Tbl_Data_2,'Tbl_Data_2'[Gate]))

Budget2 = SUM(Tbl_Data_2[Budget sales (in €)])/[Area]

The final output is shown below:

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

[Jihwan_Kim]

Hi, @Anonymous 

I am not sure how to approach your data model, but please correct me if I wrongly approached it.

Please change the measure to the below.

Budget sales per m2 = SUM(Tbl_Data_2[Budget sales (in €)])/SUM(Tbl_Data_2[Area (in m2)])

or

Budget sales per m2 =  divide (SUM(Tbl_Data_2[Budget sales (in €)]) , SUM(Tbl_Data_2[Area (in m2)]))

I think the measure you have in your pbix file is not correctly written.

Thank you.

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: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Thanks Jihwan Kim, you are right. I corrected. However, at the end I want to show the Budget sales per m2 for a single size of the respected gate sizes (in this example Gate 1A has a size of 100m2 and not making a sum. The situation should be like as follows:

I also adapted the test database accordingly. 

Link: https://www.dropbox.com/t/LlwcGhcP9CY1VjTF

Thanks again!

https://www.dropbox.com/t/LlwcGhcP9CY1VjTF