cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LayneCobain
Frequent Visitor

Average of a Measure per Category

Hey guys,

 

I have 3 simplified data tables as follows (the third is a calendar table):

 

"Umsätze Produkt"

LayneCobain_0-1662040102035.png

"Warengruppen"

LayneCobain_1-1662040125124.png

LayneCobain_5-1662040394785.png

 

First I wanted to calculate the deviation of the product prices (latest price last year compared with the max prices per month from this year).

This results in the table as follows with the help of you guys in another thread:

LayneCobain_6-1662040538487.png

 

The Deviation is calculated by following code:

 

Deviation = 
VAR CurrentPrice =
    CALCULATE (
        MAX ( 'Umsätze Produkt'[Preis] ),
        'Umsätze Produkt'[Jahr] = YEAR ( TODAY () )
    )
VAR LatestPrice =
    CALCULATE (
        LASTNONBLANKVALUE ( 'Umsätze Produkt'[Datum], SUM ( 'Umsätze Produkt'[Preis] ) ),
        PREVIOUSYEAR ( Kalender[Datum] )
    )
RETURN
    IF (
        DIVIDE ( CurrentPrice, LatestPrice ) - 1 = -1.00,
        BLANK (),
        DIVIDE ( CurrentPrice, LatestPrice ) - 1
    )

 

 

The final result I need is the average deviation per category broken down per month:

LayneCobain_7-1662040594773.png

As you can see, it's empty and I really don't know how to calculate the average of a measure...

 

Could you guys help me out?

 

Thx and greets

1 ACCEPTED SOLUTION

@LayneCobain,

 

Thanks for the file. I rewrote the measure to include additional columns in the virtual table (in case they aren't in the visual):

 

Deviation Average =
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Umsätze Produkt',
            Kalender[Year],
            Kalender[Month],
            'Umsätze Produkt'[Product],
            Warengruppen[Category]
        ),
        "@Amount", [Deviation]
    )
RETURN
    AVERAGEX ( vTable, [@Amount] )

 

DataInsights_0-1662155030459.png

Is this correct? If not, can you provide a screenshot of the expected result?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
DataInsights
Super User
Super User

@LayneCobain,

 

You could try a measure like this:

 

Deviation Average =
VAR vTable =
    ADDCOLUMNS ( VALUES ( Warengruppen[Category] ), "@Amount", [Deviation] )
VAR vResult =
    AVERAGEX ( vTable, [@Amount] )
RETURN
    vResult




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi DataInsight and thank you for your reply,

 

I tried this measure and get this result:

LayneCobain_0-1662109783383.png

When I put in the upper table it shows the exact same values but it doesn't aggregate in the table below. Is there something wrong with my implementation?

 

Greets

@LayneCobain,

 

I would need to see your pbix. Can you share a sanitized version via one of the file services like OneDrive?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@DataInsights 

 

I uploaded the file here.

 

I found several errors.

  • It only shows some values of the "Deviation Average" when the colums Year and Month are included in the table
  • The Average Deviations per month are too low. I think it includes the months without a price because it results in -100% (see the Measure "See Error"). I tried to exclude them in the Measure "Deviation" but apparently it doesn't work.
  • The matrix "Category-Month-Average Deviation" doesn't work completely...

How can we exclude the -100% and visualize the Average Deviations per months correctly?

 

Thank you for your help. Much appreciated.

@LayneCobain,

 

Thanks for the file. I rewrote the measure to include additional columns in the virtual table (in case they aren't in the visual):

 

Deviation Average =
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Umsätze Produkt',
            Kalender[Year],
            Kalender[Month],
            'Umsätze Produkt'[Product],
            Warengruppen[Category]
        ),
        "@Amount", [Deviation]
    )
RETURN
    AVERAGEX ( vTable, [@Amount] )

 

DataInsights_0-1662155030459.png

Is this correct? If not, can you provide a screenshot of the expected result?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@DataInsights 

 

Absolutely perfect!

 

Thank you!

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.