1

I have a table like this:enter image description here

I would like to obtain a single row for each combination of product, category and price (I'm not interested in the aggregation per sub product). All the product in each category have the same price.

I tried to do a group by in different ways but when I show product, category and price in power bi table visualization, it doesn't show the correct total value.

How can I do that?

This is desidered output: enter image description here

luca canonico
  • 27
  • 1
  • 8
  • What is the desired output? – smpa01 Dec 28 '21 at 05:20
  • I need a measure that summarize rows for each combination of product category and price, finally I have a table with two rows in my example: product a, catgeory a, 6,5 and product b category a 8,5. Table visualization have to show total value of column price correctly (15) – luca canonico Dec 28 '21 at 08:59

2 Answers2

2

Here's a measure that should work:

MIN PRICE =
IF( HASONEVALUE( Data[PRODUCT] ),
    MIN( Data[PRICE] ),
    SUMX( SUMMARIZE ( Data, Data[PRODUCT], Data[CATEGORY], 
                            "PRICE", MIN ( Data[PRICE] ) ),
          [PRICE] 
    )
)

The total row will return FALSE for HASONEVALUE(Data[PRODUCT] and true for the table rows. The SUMMARIZE statement replicates your table and SUMX iterates through it to calculate the correct total.

Result

TheRizza
  • 1,577
  • 1
  • 10
  • 23
2

Try this measure

_PRICE = 
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[PRODUCT],
        'Table'[CATEGORY],
        'Table'[UM],
        'Table'[PRICE]
    ),
    'Table'[PRICE]
)

S2

smpa01
  • 4,149
  • 2
  • 12
  • 23