locking a measure/cell in power bi for comparison

[rdvasisht]

 how would i compare each cell of a column with a measure value? so in excel we can lock a cell like this: $F$4 and then compare it with each cell of a column. how would we do this in power bi? the value of the measure is subject to change.

the value of the measure should update when group it with values in another column.

for eg.

product	Revenue	Site	date	measure value
A	-2	canada	sept 3, 2022	-4.89
B	13	USA	Sept 1, 2022	-4.89
C	-11	Italy	Sept 2, 2022	-4.89
A	12	Canada	Sept 6, 2022	-4.89
B	-3	USA	Sept 4, 2022	-4.89
C	4	Italy	Sept 12, 2022	-4.89

measure that i have has value = -4.89

the measure is calculated using: (stddev.s(revenue)*sqrt(1-0.89))*-1

above table is coorect when not filtered.

what is want is create a column by comparing 'Revenue' with ' Measure'. the column will have value "1" if less than measure else "0".

following table is what i want when filter for product. for e.g. you see that the measure value is updated based in the value of the reveunue

product	Revenue	Site	date	measure value
A	-2	canada	sept 3, 2022	-3.28
A	12	Canada	Sept 6, 2022	-3.28

when i created the calculated column using the measure value and applied some filter, the value of the measure does not change. i want that to update based on filter on date, site and product.

any help is appreciated

[Syndicate_Admin]

I have a similar situation, but I'm not very experienced in Power BI, I have a table and I have a total that I'd like to leave fixed something like $F 4 in Excel, but I've already tried several functions and it doesn't give me the expected result.

The column in red is the one that the power BI throws at me, and the column in green is how the percentages should look in a straight way.

How to solve this with a formula in power bi

Any help I will be grateful.

Atte ERNESTO BARBOSA, Bogota Colombia

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Anonymous]

Hi @rdvasisht ,

You can try below steps, i provide a sample for you.

1. my test table

Table:

2. create measure with below dax formula

Median Price = MEDIANX(ALL('Table'),[Price])

Measure =
VAR cur_price =
    SELECTEDVALUE ( 'Table'[Price] )
RETURN
    IF ( cur_price > [Median Price], "> median", "< median" )

3.  add a table visual with two measure and fields

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rdvasisht]

Hi @Anonymous , thanks for reply. say i have average instead of Median. now if i have multiple row for  product in table and want to filter for only fruit. it does not update the average price for selected filter. how can i have updated value of median/avg price when filterer? when filtered the average price should be only for what has been filtered. your help is appreciated

.

you see in the above photo the average price should be 15 since it is filtered for fruit only.

but the following photo shows right avg as nothing has been filtered.

[Anonymous · Accepted solution]

Hi @rdvasisht ,

Please try below steps:

1. below is my test table

Table2:

Table3:

Table 3 = VALUES('Table 2'[Product])

2. create some meaure with below dax formula

Avg Price =
VAR cur_product =
    SELECTEDVALUE ( 'Table 3'[Product] )
VAR tmp =
    FILTER ( ALL ( 'Table 2' ), 'Table 2'[Product] = cur_product )
RETURN
    IF (
        ISBLANK ( cur_product ),
        AVERAGEX ( ALL ( 'Table 2' ), [Price] ),
        AVERAGEX ( tmp, [Price] )
    )

Measure2 =
VAR cur_price =
    SELECTEDVALUE ( 'Table 2'[Price] )
RETURN
    IF ( cur_price > [Avg Price], "> Avg", "< Avg" )

Measure3 =
VAR cur_product =
    SELECTEDVALUE ( 'Table 3'[Product] )
VAR cur_p =
    SELECTEDVALUE ( 'Table 2'[Product] )
VAR tmp =
    CALCULATETABLE (
        VALUES ( 'Table 2'[Product] ),
        FILTER ( ALL ( 'Table 2' ), 'Table 2'[Product] = cur_product )
    )
RETURN
    IF ( cur_p IN tmp || ISBLANK ( cur_product ), 1, 0 )

3. add a slicer visual with Table3[Product] column, add a table visual with Table2's  column , add Measure3 to table visual filter pane

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rdvasisht]

@Anonymous thanks for your help. it worked

[HoangHugo]

Hi,

yes you can, put your measure into calculate column (this will be a new column have constant value of measure) to compare in row contex, like this

calculate column =

var meaure_column = your measure

return your comparsion