Forum Discussion
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’.
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)Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- AllisonKennedy
Community Champion
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?
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
v-yingjlCommunity 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)Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
