Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have three main tables: forecasted product sales, actual product sales, and a price list. There are relationships between the three linked on product. I have a matrix and I want to display the price for a specific product, but I am instead getting a count of or sum of the product costs as opposed to the specific value. I'm ultimately hoping to be able to create a formula to display the price times the number of a given product sold but I can't seem to get that to work either. Any ideas?
Solved! Go to Solution.
Would it work to change [Item Cost (2)] to a measure like this?
Item Cost (3) = SELECTEDVALUE(Product[Cost])
This way, if all of the products with the same Product Number cost the same, you'll get one value for cost.
If they have seperate values, how should PowerBI know which one to use for each Product Number? If this is the case, I would suggest adding cost as a secondary row, and pressing the "Expand all down one level in the hierarchy" button on the matrix to display them all.
2 things I would check first:
1) make sure the default summarization is set to 'Don't summarize'
2) in the fields tab of your matrix vissual, right click on the prices value and make sure that is also set to 'Don't summarize'
Thanks buffanut. The default summarization is set to don't summarize. When I right click on the price value in my fields tab there's no option to select don't summarize there. The data type is also formatted as a decimal number, though unlike other numeric values, there is no sigma icon next to the column title. Not sure if this is part of the issue.
the missing sigma sign is because the default summarization is set to don't summarize, if you set it to sum or another option it would re-appear. I have done a quick recreation and for the same column, in a matrix visual there is no 'don't summarize' option, however in a tabke visual it is there. if the way your data is shaped allows for it, you may want to try to use a table instead of matrix and see if selecting Don't Summarize helps.
Unfortunately a table won't really work for this case, as I need dates in the olumns and there are too many products and sales instances for a table to be fucntional. Thank you for your suggestions though!
I see. could you share some screen shots if the visual and the data you're working with, or something similar? would be a lot of help to udnerstanding all the moving pieces.
Here's an example. I'm actually calculating cost, not price/revenue.
- "Count of Cost" is the cost column in my price list. This can also show up as sum, avg, min, etc. if I change the calculation method by right clicking the field
- "Item Cost 2" is a temporary measure I have that is calculated using data in my forecast sheet. However, I would like to avoid this option as the costs change and I want to update the cost/price table as opposed to the forecast data.
I don't necessarily want my item cost to appear in a column in this matrix, I just need a way to refence the specific cost value so I can multiply it by the forecast and actual quantities. Anytime I attempt this I end up with a sum of all the costs * the sum of all the products.
Would it work to change [Item Cost (2)] to a measure like this?
Item Cost (3) = SELECTEDVALUE(Product[Cost])
This way, if all of the products with the same Product Number cost the same, you'll get one value for cost.
If they have seperate values, how should PowerBI know which one to use for each Product Number? If this is the case, I would suggest adding cost as a secondary row, and pressing the "Expand all down one level in the hierarchy" button on the matrix to display them all.
Thank you very much. It works perfectly. I think this solution should be pinned!
Thank you - this almost works! It works if I use the product number from my price list table, but not if I use the product number from my main forecast data table, which is odd since there is an established relationship between the tables via product number. I suppose I could change the matrix to have the info from the price list display, though it would also impact some of my slicers. I'll keep playing around.
Each product does have one unique price though.
Changing the relationship cross-filter direction to "both" resolved this issue. I think I'm all set. Thanks!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |