MIN value / Virtual table

[Arnault_]

Hi All,

Here is my problem.

I have a simple table with transactions with different supply centers and different prices.

I have created a table where I have the 3 supply centers and the average price for each of them.

Now I would like to display the minimum value amongst the three "average prices".

The difficulty is that the MIN value should be obtained by iterating through the 3 average prices. In the report visual (table), I would like to see what is shown in the below picture. The second column is a simple average and the third one corresponds to the MIN of the second one.

Here is a pbix file.

Here is the expected result.

Thanks in advance for your help.

[johnt75 · Accepted solution]

Price (Average) - MIN All Supply Center v2 = 
MINX (
    CALCULATETABLE(
        ADDCOLUMNS ( VALUES ( Data[supply_center] ), "Price", [Price (Average)] ),
        REMOVEFILTERS(Data)
    ),
    [Price]
)

[PabloDeheza · Accepted solution]

Hi there!

Try this!

Price (Average) - MIN All Supply Center v3 = 
VAR _Value =
MINX (
    ALL ( Data[supply_center] ),
    CALCULATE( AVERAGE( Data[unit_price] ) )
)
RETURN
IF( 
    HASONEVALUE( Data[supply_center] ),
    _Value,
    BLANK()
)

Should return the following:

Let me know if that helps!

[Arnault_]

Thanks @Greg_Deckler , I will take a look.

Cheers

[Arnault_ · Accepted solution]

I finally found the solution myslef.

Price (Average) MIN v3 = 
CALCULATE ( MINX
     ( VALUES ( Data[supply_center] ) ,
    [Price (Average)]
), ALL (Data[supply_center] )
)

[PabloDeheza · Accepted solution]

Hi there!

Try this!

Price (Average) - MIN All Supply Center v3 = 
VAR _Value =
MINX (
    ALL ( Data[supply_center] ),
    CALCULATE( AVERAGE( Data[unit_price] ) )
)
RETURN
IF( 
    HASONEVALUE( Data[supply_center] ),
    _Value,
    BLANK()
)

Should return the following:

Let me know if that helps!

[Arnault_]

That's a good solution except that the end of the code can be removed in my scenario. thanks for your input !!!

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to fix the measure like below.

Please check the below and the attached pbix file whether it suits your requirement.

Price (Average) =
IF (
    HASONEVALUE ( Data[supply_center] ),
    AVERAGE ( Data[unit_price] ),
    MINX (
        ADDCOLUMNS (
            VALUES ( Data[supply_center] ),
            "Price", CALCULATE ( AVERAGE ( Data[unit_price] ) )
        ),
        [Price]
    )
)

[johnt75 · Accepted solution]

Price (Average) - MIN All Supply Center v2 = 
MINX (
    CALCULATETABLE(
        ADDCOLUMNS ( VALUES ( Data[supply_center] ), "Price", [Price (Average)] ),
        REMOVEFILTERS(Data)
    ),
    [Price]
)

[Greg_Deckler]

@Arnault_ This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.