Matrix total : total of a column in sum and the total of the other column in average

[Anonymous]

Hi everyone,

I am looking to make a matrix that can show the weight shipped per month and the share of maritime on the total for each month/year. So far so good :

The problem is that on my totals, I do find the total weight shipped (sum) but on the total SEA% I would like to have an average for the year, but this is not the case.

For the weight I used this formula Total weight formula = SUMX(File1,File1[Total Weight])

For the Sea%, I used 2 measures : 

• Sea weight = CALCULATE(SUM(File1[Total Weight]), File1[Shipping type]=40)
• Sea% = DIVIDE([Sea weight],[Total weight formula],BLANK())

Do you have an idea for the average of the % of maritime to appear next to the sum of the weight?

Thank you.

Ilias

[v-shex-msft]

Hi @Anonymous,

You can take a look at the following link to use if statement with the filters to check current row context level and replace the calculation expressions:

Clever Hierarchy Handling in DAX - SQLBI

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@Anonymous , You can use isinscope or hasonevalue to change the grand total

https://xxlbi.com/blog/new-dax-function-isinscope/

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/