Row level calculated measure used in Group by or summarize

[AA2019]

Hi, 

I have a table/data-set like below, with country, brand, product type and year used as page filters to set context.

Measure M_Avg.Price is calculated, at row level (using AllSelected), and also at page level (which changes based on context). Index of Page level value of M_Avg.Price vs. Row Level Avg.Price is used to classify each product into Economy or Premium (if row price < page price then economy else premium).  Thus the classification changes with context - i.e. if country is filtered to UK, then page price is defined by UK and rows for UK are assigned a bucket based on above rule. From here ,i'd like to create a visual (stacked bar / column ) chart, to show the Sales Amount % of defined product type that is sold within each bucket (see pic), but am not able to figure out how to get the measure in a groupby / summarize etc.  Any suggestions/help would be much appreciated (I'm quite new to PowerBI)

Country	Brand	Product Type	Year	Product	Fact	Value	M_AvgPrice	M_Bucket
UK	A	P1	2019	A1	Sales Amount	100	5.00	Economy
UK	A	P1	2019	A2	Sales Amount	200	8.00	Economy
UK	A	P1	2019	A3	Sales Amount	300	20.00	Premium
UK	B	P1	2019	B1	Sales Amount	450	9.00	Economy
UK	B	P1	2019	B2	Sales Amount	150	5.00	Economy
UK	B	P1	2019	B3	Sales Amount	300	30.00	Premium
US	C	P2	2019	C1	Sales Amount	100	20.00	Premium
US	C	P2	2019	C2	Sales Amount	200	20.00	Premium
US	C	P2	2019	C3	Sales Amount	250	16.67	Premium
US	D	P2	2019	D1	Sales Amount	300	15.00	Premium
US	D	P2	2019	D2	Sales Amount	400	20.00	Premium
US	D	P2	2019	D3	Sales Amount	350	35.00	Premium
UK	A	P1	2019	A1	Sales Volume	20
UK	A	P1	2019	A2	Sales Volume	25
UK	A	P1	2019	A3	Sales Volume	15
UK	B	P1	2019	B1	Sales Volume	50
UK	B	P1	2019	B2	Sales Volume	30
UK	B	P1	2019	B3	Sales Volume	10
US	C	P2	2019	C1	Sales Volume	5
US	C	P2	2019	C2	Sales Volume	10
US	C	P2	2019	C3	Sales Volume	15
US	D	P2	2019	D1	Sales Volume	20
US	D	P2	2019	D2	Sales Volume	20
US	D	P2	2019	D3	Sales Volume	10

[v-eachen-msft · Accepted solution]

Hi  @AA2019 ,

Firstly, I cannot find “page price” in your question. So I guess this value is the total value of “Sales Amount” divided by the total value of “Sales Volume”.

Page Price =
IF (
    MAX ( 'Table'[Fact] ) = "Sales Amount",
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Fact] = "Sales Amount" )
        ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Fact] = "Sales Volume" )
        )
    )
)

Then I created measure  M_AvgPrice and calculated column M_Bucket.

M_AvgPrice =
IF (
    MAX ( 'Table'[Fact] ) = "Sales Amount",
    SUM ( 'Table'[Value] )
        / CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Country] = MAX ( 'Table'[Country] )
                    && 'Table'[Product] = MAX ( 'Table'[Product] )
                    && 'Table'[Brand] = MAX ( 'Table'[Brand] )
                    && 'Table'[Year]
                    && MAX ( 'Table'[Year] )
                    && 'Table'[Product Type] = MAX ( 'Table'[Product Type] )
            ),
            'Table'[Fact] = "Sales Volume"
        )
)

M_Bucket =
IF (
    ISBLANK ( [Page Price] ),
    BLANK (),
    IF (
        CALCULATE ( [M_AvgPrice], ALL ( 'Table'[Product Type] ) ) <= 'Table'[Page Price],
        "ECO",
        "PRE"
    )
)

Then a measure called “PercentageByGroup” will be need.

PercentageByGroup =
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Fact] = "Sales Amount" )
    / CALCULATE (
        SUM ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Product Type] ),
        'Table'[Fact] = "Sales Amount"
    )

At last, you can take these fields to get a stacked column visual.

The Axis is "Product Type".The legend is "M_Bucket".The value is "PercentageByGroup".

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AA2019]

Thanks - will try this and update status..

[v-eachen-msft]

Hi @AA2019 ,

Have you tested my solution? If my answer is useful, please accept my reply.Thanks!

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CheenuSing]

Hi @AA2019 ,

Can you upload data, pbix and output expected to Google / One Drive and share the link here

to find a solution.

What filter/slicer you will be using at visual level and page level of the report.

Cheers

CheenuSing

[AA2019]

Hi, 

Thank you for helping.

here's the link : https://drive.google.com/file/d/1Qb4jSvBcXQNNdjuJO4LJT5kRthoSS_ba/view?usp=sharing

Fields used as page filter = Product Type, Country , Brand and Year

Visual filters = None.  

Depending on the page filter defined context, the classification of a product can change from premium to economy etc. so this should be recalculated. 

The visual should then sum up the sales value column by each M_bucket and group by Product Type