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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
msl924
Helper I
Helper I

Why is value displaying as sum instead of specific value?

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? 

1 ACCEPTED 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.

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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. 

Anonymous
Not applicable

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! 

Anonymous
Not applicable

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. 

tempsnip.png

 

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! 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.