Matrix table cannot aggregate the values, shows blank fields

[Diva20]

Hi everyone,

My marix table shows blank values when i used the below dax expression.

My table view currently looks like this :

Calculated Column used for "Average of Issue Score 2" this - 

CALCULATE(AVERAGE('Distinct table'[Issue Score 2]), ALLEXCEPT('Distinct table', 'Distinct table'[Commodity], 'Distinct table'[Country], 'Distinct table'[% Weight], 'Distinct table'[Pillar]))

Note: Pillar is the 4 tpes of columns listed above (Cross-cutting, environmental, political, social)

Im trying to achieve a final product like this :

Calculated Column used in "Overall Risk by Country" for this - 

CALCULATE(AVERAGE('Distinct table'[Issue Score 2]), ALLEXCEPT('Distinct table', 'Distinct table'[Commodity Type], 'Distinct table'[Commodity], 'Distinct table'[Country], 'Distinct table'[% Weight]))

Note: Pillar is the 4 tpes of columns listed above (Cross-cutting, environmental, political, social)

Can you help me out.

Thanks in advance! 🙂

[v-xiaoyan-msft]

Hi，@Diva20 

Could you please show a more specific sample ？
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 

Best Regards,

Caitlyn Yan

[Diva20]

1. My requirement is to use a Matrix Visual, to display 4 types of pillars (Cross-cutting, environmental, political, social) in columns and rows will have (commodity, country, weighting, and overall risk by country. 
2. After using the below measure, my output looks something like (the attached image)
3. Calculated Column used for "Average of Issue Score 2" this -
4.  "Average of Issue Score2" =

CALCULATE(AVERAGE('Distinct table'[Issue Score 2]), ALLEXCEPT('Distinct table', 'Distinct table'[Commodity], 'Distinct table'[Country], 'Distinct table'[% Weight], 'Distinct table'[Pillar]))

Current output that im getting:

But, what i want to showcase as an end result is the (below image) - DESIRED OUTPUT

this is the required or expected output

NOTE: 

First image - COLUMN "Average of Issue Score 2" and Second image - COLUMN "Overall Risk by Country". Both are same columns. (naming is different)

The above 2 columns - "Average of Issue Score 2" and "Overall Risk by Country", they are coming from a different table (a sub table from the main table populating the DISTINCT values).

Any help will be apreciated. 

Thank you 

D--

[Ashish_Mathur]

Hi,

Write your formula as a measure (not as a calculated column formula).

[Ashish_Mathur]

Hi,

I am not clear about your requirement.  Share some data, describe the question and show the expected result.