Forum Discussion

Arnault_'s avatar
Arnault_
Resolver III
3 years ago
Solved

MIN value / Virtual table

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.

 

Need Help
  • johnt75's avatar
    johnt75
    3 years ago
    Price (Average) - MIN All Supply Center v2 = 
MINX (
    CALCULATETABLE(
        ADDCOLUMNS ( VALUES ( Data[supply_center] ), "Price", [Price (Average)] ),
        REMOVEFILTERS(Data)
    ),
    [Price]
)
  • PabloDeheza's avatar
    PabloDeheza
    3 years ago

    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_'s avatar
    Arnault_
    3 years ago

    I finally found the solution myslef.

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

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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.

  • johnt75's avatar
    johnt75
    Super User
    3 years ago
    Price (Average) - MIN All Supply Center v2 = 
MINX (
    CALCULATETABLE(
        ADDCOLUMNS ( VALUES ( Data[supply_center] ), "Price", [Price (Average)] ),
        REMOVEFILTERS(Data)
    ),
    [Price]
)
  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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]
    )
)
  • PabloDeheza's avatar
    PabloDeheza
    Solution Sage
    3 years ago

    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_'s avatar
      Arnault_
      Resolver III
      3 years ago

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

  • Arnault_'s avatar
    Arnault_
    Resolver III
    3 years ago

    I finally found the solution myslef.

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