Forum Discussion

newgirl's avatar
newgirl
Post Patron
1 year ago
Solved

Minimum for ALL

I have this sample dataset:

DateIDStep 1Step 2Step 3YearMonth

1/1/20241311202401
1/1/20242231202401
1/2/20243321202401
1/5/20244012202401
2/10/20245311202402
2/20/20246331

202402

 

and the following measures:

 

 

Step1_Average = AVERAGE(Requests[Step 1])
Step1_MIN = 
var _SummarizedTable = ADDCOLUMNS(SUMMARIZE(Requests,Requests[YearMonth]),"_X", [Step1_Average])
RETURN MINX(_SummarizedTable,[_X])

 

 

 

Here's the matrix I have:

Reality:

 

 

 

Step1_MIN is supposed to get the MINIMUM from month-level only. It works, because it displays 2.00 when I put it in a spearate visual. But now, how to show the value as repeating, regardless of its row fields? I need it like that because I'm going to use it for a new measure.

 

I've tried using ALL in the Step1_MIN, but it says it only accepts table reference (not table expression). I've tried using CALCULATE + ALL as a new line Step 1_MIN but it says I could only use columns from the same table.

 

 

Expectation:

 

 

 

  • Hi newgirl 
    Firstly, it is recommended to disable the automatic date hierarchies and create a calendar table that is connected to your transactions table.
    This will allow for much more efficient and flexible work.

    After you have this model you can use the formulas :

    step1_avg = AVERAGE('Table'[Step 1])
    and for minimum months average :
    min_average =
     if([step1_avg]<>BLANK(),CALCULATE(
    Minx(SUMMARIZE(VALUES(caledar[monthYear]),caledar[monthYear],"_x",[step1_avg]),[_x]),ALLSELECTED('caledar')),BLANK())
    Result:

    The pbix is attached

    more information about creating date table here :
    https://iterationinsights.com/article/how-to-create-a-date-table-in-power-bi/

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     

1 Reply

  • Hi newgirl 
    Firstly, it is recommended to disable the automatic date hierarchies and create a calendar table that is connected to your transactions table.
    This will allow for much more efficient and flexible work.

    After you have this model you can use the formulas :

    step1_avg = AVERAGE('Table'[Step 1])
    and for minimum months average :
    min_average =
     if([step1_avg]<>BLANK(),CALCULATE(
    Minx(SUMMARIZE(VALUES(caledar[monthYear]),caledar[monthYear],"_x",[step1_avg]),[_x]),ALLSELECTED('caledar')),BLANK())
    Result:

    The pbix is attached

    more information about creating date table here :
    https://iterationinsights.com/article/how-to-create-a-date-table-in-power-bi/

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly