Forum Discussion
Making a pivot table
Use this measure:
Gross Sales by min Sale Price = VAR _minPrice = MIN ( financials[Sale Price] ) VAR _MinPriceAll = CALCULATE ( MIN ( financials[Sale Price] ), ALL ( 'Product Table' ) ) RETURN CALCULATE ( SUM ( financials[Gross Sales] ), FILTER ( 'Product Table', _minPrice = _MinPriceAll ) )
Well it depends on what the definition of lowest price is. If it is the price resulting from dividing sales by the number of units sold, then...
I'm working with this model structure:
To get the sales price by product for since the beginning of time:
Price by Product all years =
CALCULATE (
DIVIDE ( SUM ( financials[ Sales] ), SUM ( financials[Units Sold] ) ),
ALL ( 'Dates Table' )
)
and the lowest for all products
Lowest Sales Price all products =
MINX(ALL('Product Table'[dProduct]), [Price by Product all years])
and finally the sales by month for the lowest selling price product
Gross Sales for lowest Price Porduct =
SUMX (
'Dates Table',
CALCULATE (
SUM ( financials[Gross Sales] ),
FILTER (
'Product Table',
[Price by Product all years] = [Lowest Sales Price all products]
)
)
)
If you want to see the excercise by year...
Low price by year =
CALCULATE (
DIVIDE ( SUM ( financials[ Sales] ), SUM ( financials[Units Sold] ) ),
ALL ( 'Dates Table'[Month] )
)
Lowest price by Year =
MINX(ALL('Product Table'[dProduct]), [Low price by year])Gross Sales for lowest Price Porduct by year =
SUMX (
VALUES ( 'Dates Table'[Month] ),
CALCULATE (
SUM ( financials[Gross Sales] ),
FILTER ( 'Product Table', [Low price by year] = [Lowest price by Year] )
)
)
Sample PBIX file attached
Oh wow! That's a really great job, but actually I hoped if you knew how to build something like this:
Month ------- Min price ------- Product name ------- Gross priceJanury ------- 7 ------- Montana ------- 10k
Janury ------- 7 ------- Paseo ------- 20k
February ----- 7 ------- VTT ------- 10k
March ------- 7 ------- Montana ------- 10k
and etc.
All numbers except min price are random. So the idea was to get one or more data from each month by product. I mean I need a table where there are 12 months and next column contain minimum price per each month (Price = Sale price column). And then comes the name of all products that has the same price (with gross sales after it - but it's easy, just calculate Sum of...) Hope you got me🥺
I was expecting to get something like this, but to have table that contains only price = 7 you need to add filter which is irrational and affects another data on the wallpaper 😞
I can also take first N rows that have 7 in the second column but it's shady too...
Use this measure:
Gross Sales by min Sale Price = VAR _minPrice = MIN ( financials[Sale Price] ) VAR _MinPriceAll = CALCULATE ( MIN ( financials[Sale Price] ), ALL ( 'Product Table' ) ) RETURN CALCULATE ( SUM ( financials[Gross Sales] ), FILTER ( 'Product Table', _minPrice = _MinPriceAll ) )You are amazing! That's exactly what I wanted!
