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
PujaPal
New Member

VALUES getting aggregated in Power BI

Hi team,

Hope everyone is doing good! I need some help to resolve the below issue:

 

  • I am using Azure Analysis Service with Power BI and I have a matrix visual with 5 fields as ROWS(ID, Name, Loc, Des, Age), 1 field as COLUMN which will work as column name(pivoted columns) and finally 1 field which is used as VALUES for those columns.
  • For 2 data entries, those 5 fields(ID, Name, Loc, Des, Age) have exactly the same value but the VALUES field have distinct values. In AAS it is displayed as 2 individual entries but when it comes to Power BI only single row was coming and the values were displayed as “First values”.
  • To resolve this, summarization was enabled in AAS but now I am still getting only a single entry of the data but VALUES are getting summed up/aggregated.

 

Example:

  • In AAS it looks like this and I expect the same thing to be displayed in Power BI as well:

ID

Name

Loc

Des

Age

Value 1

Value 2

Value 3

1

ABC

X

Y

30

32

245

400

1

ABC

X

Y

30

34

342

876

 

  • When summarization was not enabled in AAS, in Power BI it was coming as a single row where the VALUES field was taking the ‘First Value’:

ID

Name

Loc

Des

Age

Value1

Value 2

Value 3

1

ABC

X

Y

30

32

245

400

 

  • After summarization in AAS, In Power BI it still comes as a single row but summed up value (32 +34 = 66), and so on:

ID

Name

Loc

Des

Age

Value1

Value 2

Value 3

1

ABC

X

Y

30

66

587

1276

 

 

Can anyone help me with this how I can avoid this aggregation in Power BI and get two separate entries of the row without their values getting summed up? Or anything I can do in AAS to resolve this?

“Don’t summarize” option doesn’t come for VALUES field in Matrix visual so it is coming as ‘SUM’.

 

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @PujaPal ,
As @AllisonKennedy  mentioned, Matrix is for aggregating values. We cannot show not summarize data in it directly.

However, you can create a calculated column in your AAS model based on some conditions like rank on value1... and show them in the matrix visual to avoid aggregating indirectly.

index = RANKX('Sheet1','Sheet1'[Value1],,DESC,Dense)

matrix.png

 

Best Regards,
Yingjie Li

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

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @PujaPal ,
As @AllisonKennedy  mentioned, Matrix is for aggregating values. We cannot show not summarize data in it directly.

However, you can create a calculated column in your AAS model based on some conditions like rank on value1... and show them in the matrix visual to avoid aggregating indirectly.

index = RANKX('Sheet1','Sheet1'[Value1],,DESC,Dense)

matrix.png

 

Best Regards,
Yingjie Li

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

AllisonKennedy
Super User
Super User

Matrix is for aggregating values. What is the field you're putting in Columns doing? Is it possible to use a table visual instead of matrix?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Actually I need to show headers also on top of the columns, so I am using matrix 😞 

So under a single header, 5-6 columns has to be shown, so I had to opt for matrix.

 

 

Regards,

Puja

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.