Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi supporting team!
I would like to request for the help with preparing formula for the measure to calculating variance from minimum price value (BU Var%), but not from whole population just for specific criterion (BU).
Please see below screen with data and formula in Excel.
Thanks to @daxer-almighty I have formula in Power BI for calculating the variance (Upcharge column in Excel with example formula: =O2/MIN($O$2:$O$14)-1) for the whole population, keeping current filter in the slicers, but taking into account that PO Price is per BU, Company, Vendor Name):
Upcharge =
IF( ISINSCOPE( Data_17_21_table[Vendor Name]),
var vMinPOPrice =
MINX(
ALLSELECTED( Data_17_21_table[BU], Data_17_21_table[Company], Data_17_21_table[Vendor Name] ),
[PO Price] )
var vResult =
DIVIDE( vMinPOPrice, vMinPOPrice )
* ( DIVIDE( [PO Price], vMinPOPrice ) - 1 )
RETURN
vResult)
This is proper one and taking minimum PO Price from all BUs. In new value I need minimum value, but only from the same BU. Could you help?
Thank you,
rhoex
Solved! Go to Solution.
// Something like this?
[BU Var%] =
IF(
ISINSCOPE( Data_17_21_table[Vendor Name] )
&&
ISINSCOPE( Data_17_21_table[BU] ),
var vCurrentBU = SELECTEDVALUE( Data_17_21_table[BU] )
VAR vMinPOPrice =
MINX(
FILTER(
ALLSELECTED(
Data_17_21_table[BU],
Data_17_21_table[Company],
Data_17_21_table[Vendor Name]
),
Data_17_21_table[BU] = vCurrentBU
),
[PO Price]
)
VAR vResult =
DIVIDE(
vMinPOPrice,
vMinPOPrice
)
* (
DIVIDE(
[PO Price],
vMinPOPrice
) - 1
)
RETURN
vResult
)
divide([PO Price],minx(filter(allselect(Data_17_21_table), Data_17_21_table[BU]=max(Data_17_21_table[BU])),[PO Price]))
divide(max(table[po price]),calculate(min(table[po price]),filter(allselect(table),table[bu]=max(table[bu]))))
Thank you @wdx223_Daniel ,
Currently I am receiving an error:
Failed to resolve name 'allselect'. It is not a valid table, variable, or function name.
Could you help?
Thx,
rhoex
it should be ALLSELECTED instead of allselect
Thank you @wdx223_Daniel !
I think we are almost there. Just the formula is not taking something into account, because still as a reference value (second part in divide formula) is taking not minumum PO Price from the specific BU. There are no hidden filters, just slicered value and is not showing proper minumum price.
Please look into results:
Last column (BU Var% 2) should show minimim price from the specific BU. In first instance should be seen 0.0908 not 0.885, in second 0.0900 not 0.885, in third 0.841 not 0.820.
The formula for this is:
BU Var% 2 =
minx(filter(allselected(Data_17_21_table), Data_17_21_table[BU]=max(Data_17_21_table[BU])),[PO Price])
Maybye it's not taking into account slicers filters (Generic SKU, Reuse Flag, Period - Last 12M and Period) (?)
rhoex
Does anyone have an idea what I could change in the formula?
Thank you in advance
rhoex
// Something like this?
[BU Var%] =
IF(
ISINSCOPE( Data_17_21_table[Vendor Name] )
&&
ISINSCOPE( Data_17_21_table[BU] ),
var vCurrentBU = SELECTEDVALUE( Data_17_21_table[BU] )
VAR vMinPOPrice =
MINX(
FILTER(
ALLSELECTED(
Data_17_21_table[BU],
Data_17_21_table[Company],
Data_17_21_table[Vendor Name]
),
Data_17_21_table[BU] = vCurrentBU
),
[PO Price]
)
VAR vResult =
DIVIDE(
vMinPOPrice,
vMinPOPrice
)
* (
DIVIDE(
[PO Price],
vMinPOPrice
) - 1
)
RETURN
vResult
)
Thanks @wdx223_Daniel ,
I've translated your suggestion into:
but I've got error:
Column 'PO Price' in table 'Data_17_21_table' cannot be found or may not be used in this expression.
I forgot to mention that 'PO Price' is already a measure. It's formula is:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |