admin管理员组文章数量:1122826
I have the following two tables in PowerBI:
Counterparty Commodity DealType Delivery Period Value Price
MQ HO Buy Nov24 -3.59528 20.6947
MQ HO Sell Nov24 1.243251 20.6947
MQ HO Sell Nov24 1.947759 20.6947
MQ HO Sell Nov24 2.346524 20.6947
MQ HO Buy Dec24 -3.19765 21.4649
MQ HO Sell Dec24 1.123275 21.4649
MQ HO Sell Dec24 1.759798 21.4649
MQ HO Sell Dec24 2.145425 21.4649
JA HO Buy Jul24 -1.65421 21.1075
JA HO Buy Jul24 -3.03272 21.1075
JA HO Sell Jul24 1.597610 21.115
JA HO Sell Jul24 2.575670 21.115
JA HO Sell Jul24 1.751550 21.115
Counterparty Commodity DealType Delivery Period Value Price
MQ HO Buy Nov24 -3.60305 20.6947
MQ HO Sell Nov24 1.245915 20.6947
MQ HO Sell Nov24 1.951933 20.6947
MQ HO Sell Nov24 2.351525 20.6947
MQ HO Buy Dec24 -3.21755 21.4649
MQ HO Sell Dec24 1.130265 21.4649
MQ HO Sell Dec24 1.770748 21.4649
MQ HO Sell Dec24 2.158775 21.4649
JA HO Buy Nov24 -8.76732 21.0794
JA HO Buy Nov24 -1.60734 21.0794
JA HO Buy Dec24 -1.43772 21.0794
JA HO Buy Dec24 -7.84212 21.0794
JA HO Sell Nov24 8.4061 21.0794
JA HO Sell Nov24 9.14049 21.0794
JA HO Sell Nov24 1.35081 21.0794
JA HO Sell Dec24 1.23526 21.0794
JA HO Sell Dec24 7.6351 21.0794
JA HO Sell Dec24 8.44659 21.0794
In my model view, I have these two tables as my fact tables, and I have two dim tables for Counterparty and Commodity with just the distinct values.
When I create a matrix with this data, I am grouping Counterparty, Commodity, DealType, and Delivery period to be my rows field
Columns field is empty
My Values field is Price (from table1), Price (from table2), Sum of Value (from table1), Sum of Value (from table2)
However, the values are not displaying correctly. Firstly, the Price columns are forced to be aggregated. Secondly, for Counterparty = JA you can see the Delivery Period does not align correctly between the two tables so that will cause issues displaying in the matrix correctly.
My question is, what kind of measure(s) can I create so that my data is grouped and displayed properly?
My expected output (excluding the sums of Value since I am more concerned with the Prices but Feel free to add the sums too):
Price2 Price1
Counterparty Commodity DealType Delivery period
JA HO Buy Dec24 21.0794 21.1075
Nov24 21.0794 21.1075
Sell Dec24 21.0794 21.115
Nov24 21.0794 21.115
MQ HO Buy Dec24 21.4649 21.4649
Nov24 20.6939 20.6939
Sell Dec24 21.4649 21.4649
Nov24 20.6939 20.6939
I have the following two tables in PowerBI:
Counterparty Commodity DealType Delivery Period Value Price
MQ HO Buy Nov24 -3.59528 20.6947
MQ HO Sell Nov24 1.243251 20.6947
MQ HO Sell Nov24 1.947759 20.6947
MQ HO Sell Nov24 2.346524 20.6947
MQ HO Buy Dec24 -3.19765 21.4649
MQ HO Sell Dec24 1.123275 21.4649
MQ HO Sell Dec24 1.759798 21.4649
MQ HO Sell Dec24 2.145425 21.4649
JA HO Buy Jul24 -1.65421 21.1075
JA HO Buy Jul24 -3.03272 21.1075
JA HO Sell Jul24 1.597610 21.115
JA HO Sell Jul24 2.575670 21.115
JA HO Sell Jul24 1.751550 21.115
Counterparty Commodity DealType Delivery Period Value Price
MQ HO Buy Nov24 -3.60305 20.6947
MQ HO Sell Nov24 1.245915 20.6947
MQ HO Sell Nov24 1.951933 20.6947
MQ HO Sell Nov24 2.351525 20.6947
MQ HO Buy Dec24 -3.21755 21.4649
MQ HO Sell Dec24 1.130265 21.4649
MQ HO Sell Dec24 1.770748 21.4649
MQ HO Sell Dec24 2.158775 21.4649
JA HO Buy Nov24 -8.76732 21.0794
JA HO Buy Nov24 -1.60734 21.0794
JA HO Buy Dec24 -1.43772 21.0794
JA HO Buy Dec24 -7.84212 21.0794
JA HO Sell Nov24 8.4061 21.0794
JA HO Sell Nov24 9.14049 21.0794
JA HO Sell Nov24 1.35081 21.0794
JA HO Sell Dec24 1.23526 21.0794
JA HO Sell Dec24 7.6351 21.0794
JA HO Sell Dec24 8.44659 21.0794
In my model view, I have these two tables as my fact tables, and I have two dim tables for Counterparty and Commodity with just the distinct values.
When I create a matrix with this data, I am grouping Counterparty, Commodity, DealType, and Delivery period to be my rows field
Columns field is empty
My Values field is Price (from table1), Price (from table2), Sum of Value (from table1), Sum of Value (from table2)
However, the values are not displaying correctly. Firstly, the Price columns are forced to be aggregated. Secondly, for Counterparty = JA you can see the Delivery Period does not align correctly between the two tables so that will cause issues displaying in the matrix correctly.
My question is, what kind of measure(s) can I create so that my data is grouped and displayed properly?
My expected output (excluding the sums of Value since I am more concerned with the Prices but Feel free to add the sums too):
Price2 Price1
Counterparty Commodity DealType Delivery period
JA HO Buy Dec24 21.0794 21.1075
Nov24 21.0794 21.1075
Sell Dec24 21.0794 21.115
Nov24 21.0794 21.115
MQ HO Buy Dec24 21.4649 21.4649
Nov24 20.6939 20.6939
Sell Dec24 21.4649 21.4649
Nov24 20.6939 20.6939
- price 1 from the secnd table and price 2 from the first table table? why JA HO sell Dec 24 for prices are 21.0794 and 21.115? we can only find 21.0794 in the second table – Ryan Commented Nov 25, 2024 at 1:43
- @Ryan I think that’s just a typo. But yes so I need to get the group JA HO sell Dec24 from each table and display the price. For JA tho, the date does not align correctly in table 1. In that case, you take the price where it is sell or buy and apply it for Price 1 in the final output – iBeMeltin Commented Nov 25, 2024 at 5:39
- it's better to update the output. We need to find out the logic between the sample data and expected output. If they are not matching, that will make us to provide the wrong answer. – Ryan Commented Nov 25, 2024 at 5:51
- @Ryan it is updated – iBeMeltin Commented Nov 25, 2024 at 13:54
1 Answer
Reset to default 0here is a workaround for you create two columns in each table
Column = "Table1"
Column = "Table2"
Then combine two tables
Table = UNION(Table1,Table2)
Then create two measures for price
price1 =
MAXX ( FILTER ( 'Table', 'Table'[Column] = "Table1" ), 'Table'[Price] )
price2 =
MAXX ( FILTER ( 'Table', 'Table'[Column] = "Table2" ), 'Table'[Price] )
本文标签： powerbiIssue displaying correct values in matrixStack Overflow
版权声明：本文标题：powerbi - Issue displaying correct values in matrix - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736301324a1931138.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论