Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team!
I would like to request you for the help with formula to get minimum value from the measure.
As a final result I would like to calculate the difference from minimum PO Price from all companies.
In Excel it's easy and looks like:
However in PowerBI I stuck with getting that minimum value 0,8333 for all companies.
My results by error and trial there are following and nothing from them are correct.
Visualization has 3 Slices, which should be taken into account: Period, Reuse Flag and Generic SKU
PO Price =
DIVIDE(
SUM('Data_17_21_table'[PO Amount]),
SUM('Data_17_21_table'[PO Qty])
)
My trials with minimum value:
Min1 =
minx(VALUES(Data_17_21_table),[PO Price])
Min2 =
MINX(
'Data_17_21_table',
CALCULATE([PO Price])
)
Min3 =
MINX(
KEEPFILTERS(VALUES('Data_17_21_table'[FinYear])),
CALCULATE([PO Price])
)
Min4 =
MINX(
KEEPFILTERS(VALUES('Data_17_21_table'[BU])),
CALCULATE([PO Price])
)
Min5 = minX(Data_17_21_table, DIVIDE(
SUM('Data_17_21_table'[PO Amount]),
SUM('Data_17_21_table'[PO Qty])
))
Min6 = CALCULATE(minx(VALUES(Data_17_21_table),[PO Price]), ALLEXCEPT(Data_17_21_table, Data_17_21_table[Reuse Flag], Data_17_21_table[Period], Data_17_21_table[Generic Sku]))
Min7 = CALCULATE(minx(VALUES(Data_17_21_table),[PO Price]), ALL(Data_17_21_table))
Min8 = CALCULATE(minx(VALUES(Data_17_21_table),[PO Price]), ALLCROSSFILTERED(Data_17_21_table))
Min9 =
CALCULATE(MINX(VALUES(Data_17_21_table),[PO Price]),ALLEXCEPT(Data_17_21_table, Data_17_21_table[Generic Sku], Data_17_21_table[Period], Data_17_21_table[Reuse Flag]))
Could you suggest another approach?
Thank you in advance!
rhoex
Solved! Go to Solution.
[Diff from MIN] =
// ISINSCOPE guarantees that the visual
// you use the measure in uses T[Company]
// for grouping. Please DO NOT use this
// measure in other measures, especially
// those that include any explicit iterator.
// THIS IS A TOP-LEVEL MEASURE.
IF( ISINSCOPE( T[Company] ),
var vMinPOPrice =
MINX(
ALLSELECTED( T[Company] ),
[PO Price]
)
var vResult =
// This first DIVIDE makes sure BLANK
// is returned when vMinPOPrice is 0.
DIVIDE( vMinPOPrice, vMinPOPrice )
* ( DIVIDE( [PO Price], vMinPOPrice ) - 1 )
RETURN
vResult
)
Hi @rhoex ,
I believe you have defined "M_Final_1 = DIVIDE([PO Price],CALCULATE(MIN(Data_17_21_table[PO Price]),ALL(Data_17_21_table)),0)-1"
[PO Price]
as a measure. Can you please confirm that you have used the following pattern:
M_Final_1 {Required Measure's Name }= DIVIDE({Measure of PO Price's Sum (or any other aggregation suiting your requirements},CALCULATE(MIN({Specificy PO Price Column here}),ALL(Data_17_21_table)),0)-1
I forgot to mention and maybe this is important. PowerBI screen is showing already visualisation in Matrix. This is not table from Data. In Data_17_21_table I have much more transactions and in Matrix I am summarising this per Company
[Diff from MIN] =
// ISINSCOPE guarantees that the visual
// you use the measure in uses T[Company]
// for grouping. Please DO NOT use this
// measure in other measures, especially
// those that include any explicit iterator.
// THIS IS A TOP-LEVEL MEASURE.
IF( ISINSCOPE( T[Company] ),
var vMinPOPrice =
MINX(
ALLSELECTED( T[Company] ),
[PO Price]
)
var vResult =
// This first DIVIDE makes sure BLANK
// is returned when vMinPOPrice is 0.
DIVIDE( vMinPOPrice, vMinPOPrice )
* ( DIVIDE( [PO Price], vMinPOPrice ) - 1 )
RETURN
vResult
)
Hi ,
Replace the column with measures. Also use allexcept to remove effect of all filters except the ones the you want to apply (you have to write the three filter columns in all except ).
Thanks @Anonymous
Did you mean something like this?
M_Final_1 = DIVIDE([PO Price],CALCULATE(MIN(Data_17_21_table[PO Price]),ALL(Data_17_21_table)),0)-1
I have information: Column 'PO Price' in table 'Data_17_21_table' cannot be found or may not be used in this expression.
and with allexcept, the same error
M_Final_2 = DIVIDE([PO Price],CALCULATE(MIN(Data_17_21_table[PO Price])ALLEXCEPT(Data_17_21_table, Data_17_21_table[Generic Sku], Data_17_21_table[Period], Data_17_21_table[Reuse Flag])),0)-1
So I understand for measure I should use MINX function, and:
M_Final_3 = DIVIDE([PO Price],CALCULATE(MINX(VALUES(Data_17_21_table),[PO Price]),ALLEXCEPT(Data_17_21_table, Data_17_21_table[Generic Sku], Data_17_21_table[Period], Data_17_21_table[Reuse Flag])),0)-1
But this returns values not as I would expect. Actually it's similar approach as in Min9 above, so no proper minimum value taken 😞
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |