Forum Discussion
Minimum for ALL
I have this sample dataset:
DateIDStep 1Step 2Step 3YearMonth
| 1/1/2024 | 1 | 3 | 1 | 1 | 202401 |
| 1/1/2024 | 2 | 2 | 3 | 1 | 202401 |
| 1/2/2024 | 3 | 3 | 2 | 1 | 202401 |
| 1/5/2024 | 4 | 0 | 1 | 2 | 202401 |
| 2/10/2024 | 5 | 3 | 1 | 1 | 202402 |
| 2/20/2024 | 6 | 3 | 3 | 1 | 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
- Ritaf1983Super User
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