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
pbeeyeqs
Helper I
Helper I

Monthly Avg Compared to Separate Avg

I have Sampledata table showing price changes,

 

pic1.png

 

A matrix has been created showing price for each month.

 

pic2.png

 

I also have a table that shows yearly CPI.

 

pic3.png

 

What I would like is to highlight the prices on table one where the PRICE CHANGE is 1st table has a higher percent change than the previous year CPI.

 

For example, MODEL #1 in the first table would have JAN 2019 shaded because it is a price increase of 13.33% and 2018 CPI AVG is 1.910%. Also, MARCH 2019 would be shaded because it is a price increase of 23.53%, again, above the 2018 AVG CPI of 1.910.

 

Hope this makes sense.

 

SAMPLE FILE

8 REPLIES 8
Icey
Community Support
Community Support

Hi @pbeeyeqs ,

Maybe you can create your measures like so:

Effective Price of the previous month =
VAR a =
    MAX ( DatesTable[Date].[MonthNo] ) - 1
VAR b =
    MAX ( DatesTable[Date].[Year] ) - 1
RETURN
    IF (
        a <> 0,
        CALCULATE (
            MAXX ( DatesTable, [EFFECTIVE PRICE] ),
            FILTER (
                ALL ( DatesTable ),
                a = DatesTable[Date].[MonthNo]
                    && b + 1 = DatesTable[Date].[Year]
            )
        ),
        CALCULATE (
            MAXX ( DatesTable, [EFFECTIVE PRICE] ),
            FILTER (
                ALL ( DatesTable ),
                b = DatesTable[Date].[Year]
                    && DatesTable[Date].[MonthNo] = 12
            )
        )
)
PRICE CHANGE =
DIVIDE (
    [EFFECTIVE PRICE] - [Effective Price of the previous month],
    [Effective Price of the previous month]
)
If Price Change is higher than previous year =
IF ( [PRICE CHANGE] > MAX ( 'CPI YRLY AVG'[AVG] ), 1, 0 )

Monthly Avg Compared to Separate Avg.PNG

This is my PBIX file.

 

Best Regards,

Icey

 

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

Ashish_Mathur
Super User
Super User

Hi,

How have you calculated the CPI (%) change as 1.910% for 2018.  If i apply a filter on 2019 in the CPI Table, the simple average is 254.72.  If i apply a filter on 2018 in the same table, the simpel average is 251.10.  The % change should be 1.44%.  Please clarify.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

The CPI is yearly avg, not monthly avg for the year. 

 

Take (DEC 2018 - DEC 2017) / DEC 2017

 

(251.233 - 246.524) / 246.524 = 1.91%

 

Sorry I didn't clarify.

Hi,

Thank you for clarifying.  The Growth in effective price (%) in January 2019 is 13.33%.  In January 2019, the Growth in CPI (%) should be

=(Jan 2019 CPI - Jan 2018 CPI)/Jan 2018 CPI

This figure is 1.55%.

Since 13.33% > 1.55%, the 13.33% cell should be coloured.  Is my understanding correct?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yes, for MODEL #1 the price change is 13.33% in Jan 2019, so that cell should be colored. And, March 2019 would be colored because the increase is 23.53%, which is also greater than the 1.55%

 

Another example, Product #7, July 2018 is colored because the price increase is 100% and that is greater than the CPI of

(JAN 2018 - JAN 2017) / JAN 2017

 

 

Hi,

Why should the Growth in Effective price in July 2018 be compared with the growth in CPI for January.  The coparison should be with the figure that we get by the following equeation

=(JULY 2018 - JULY 2017) / JULY 2017

Please confirm.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

OK - had a response then had a discussion regarding your point: if you think I should add as new question, I'm fine with that.

 

So, say we choose MODEL #1. We want to pick our timeline, MM/YY to MM/YY

 

We would want 2 results

1) What is the percent price change of MODEL #1 during that time period

2) What is the percent price change of the CPI during that same period

 

 

 

Ultimately, what I would be doing next is charting the 2 percentages over time (monthly) on the same graph. 

Hi,

I am confused now.  For 4/5 months, please show me the exact result you are expecting with an explanation so that i can tally my answers with your expected results.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

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