Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Diva20
Frequent Visitor

Matrix table cannot aggregate the values, shows blank fields

Hi everyone,

 

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

 

My table view currently looks like this :

MAtrix issue'.PNG

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 :

issue with matrix.PNG

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! 🙂

 

 

 

 

4 REPLIES 4
v-xiaoyan-msft
Community Support
Community Support

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
Frequent Visitor

  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:

MAtrix issue'.PNG

 

 

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

this is the required or expected outputthis 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--

 

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.