cancel
Showing results for 
Search instead for 
Did you mean: 
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

9 REPLIES 9
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 -  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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.