CODEWITHSUNDEEP
powerbidaxdata-modeling
Zax
Zax

Reputation: 13

Power BI, show category average rows in same table

I have this raw data from source:

Category   Product  Price
C1         P1         1
C1         P2         1
C1         P3         4
C2         P4         2
C2         P5        10
C2         P6        12

I want to visualise a Power BI table that shows the Category average within the same structure:

Category   Product  Price
C1         P1         1
C1         P2         1
C1         Avg_C1     3     
C1         P3         4
C2         P4         2
C2         Avg_C2     8
C2         P5        10
C2         P6        12

Many thanks if you show me a solution.

Just to reformat the question...

I have this raw data from source:

**Category Product Price** 
C1        P1      1 
C1        P2      1 
C1        P3      4 
C2        P4      2 
C2        P5     10 
C2        P6     12

I want to visualise a Power BI table that shows the Category average within the same structure:

**Category Product Price**  
C1        P1        1 
C1        P2        1 
C1        Avg_C1    3
C1        P3        4 
C2        P4        2 
C2        Avg_C2    8 
C2        P5       10 
C2        P6       12

Upvotes: 0

Views: 537

Answers (1)

David Gerard
David Gerard

Reputation: 29

You can create a Matrix visual in which you place the Category and Product columns on Rows and for Values you use a Measure that would be like this:

PriceWithAverage =
VAR CurrentCategory =
    MAX ( ProductTable[Category] )
RETURN
    IF (
        ISFILTERED ( ProductTable[Product] ),
        MAX ( ProductTable[Price] ),
        CALCULATE (
            AVERAGE ( ProductTable[Price] ),
            FILTER ( ProductTable, ProductTable[Category] = CurrentCategory )
        )
    )

Let us know if that works for you

Best

David

Upvotes: 1

Related Questions