Making a pivot table

[Michael_nik]

Hi everyone!

I'm new in power bi, but I have a strong basis in Pyton. I was given a simple task to make a table from sample one which is given in Power BI by default. So the task is to calculate the Product with lowest Sale Price and corresponding to this Product and Gross Sales for each month.

It looks easy to do it in python by making a groupby func with using min, but I have no idea how to add the corresponding product to it in PowerBI, because there can be more than one product wich has the lowest price = 7.

Hope I will able to get the answer in 2 days!

P.S. I already made a auxiliary table called Calendar, so now there are two tables: financials and Calendar that can be used to answer the question.

[PaulDBrown]

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

[Michael_nik]

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

[PaulDBrown · Accepted solution]

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 )
    )

[Michael_nik]

You are amazing! That's exactly what I wanted!

[Michael_nik]

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 price

Janury  -------               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🥺

[PaulDBrown]

Can you share sample data please?

[Michael_nik]

To get the data, you just need to open PowerBI desktop, then choose sample data!

Hope you know how to help me, because I no longer have the strength to accomplish this task