What if parameter based on slicer selection

[alan7lp]

Hello there,

I have created a what if parameter that works as it's supposed to: based on the % selected it increases/decreases the amount in PCS and $:

Parameter = SELECTEDVALUE('Adjusted Need'[Parameter Value], 0)

Adjusted Need in PCS = [NEED] * (1 + 'Adjusted Need'[Parameter Value])

Adjusted Need in $ = [NEED in $] * (1 + 'Adjusted Need'[Parameter Value])

With a -22% selected in the parameter this is the result:

PRODUCT	NEED IN $	ADJUSTED NEED IN $	NEED IN PCS	ADJUSTED NEED IN PCS
PRODUCT A	€ 17,459,152	€ 13,618,138	546,898	426,580
PRODUCT B	€ 16,781,109	€ 13,089,265	785,704	612,849
PRODUCT C	€ 8,278,878	€ 6,457,525	121,028	94,402
PRODUCT D	€ 13,693,814	€ 10,681,175	152,047	118,597
PRODUCT E	€ 3,033,191	€ 2,365,889	108,769	84,840
PRODUCT F	€ 4,846,410	€ 3,780,200	44,264	34,526
PRODUCT G	€ 9,244,541	€ 7,210,742	110,160	85,925
PRODUCT H	€ 1,060,012	€ 826,810	45,672	35,624
total:	€ 74,397,107	€ 58,029,744	1,914,542	1,493,343

Now, what I am after is if having an slicer for the PRODUCT category and selecting 1 or more, show the increase/decrease only on those products selected, example:

PRODUCT A and B selected on a slicer with an increase of 10% then:

PRODUCT	NEED IN $	ADJUSTED NEED IN $	NEED IN PCS	ADJUSTED NEED IN PCS
PRODUCT A	€ 17,459,152	€ 19,205,067	546,898	601,588
PRODUCT B	€ 16,781,109	€ 18,459,220	785,704	864,274
PRODUCT C	€ 8,278,878	€ 8,278,878	121,028	121,028
PRODUCT D	€ 13,693,814	€ 13,693,814	152,047	152,047
PRODUCT E	€ 3,033,191	€ 3,033,191	108,769	108,769
PRODUCT F	€ 4,846,410	€ 4,846,410	44,264	44,264
PRODUCT G	€ 9,244,541	€ 9,244,541	110,160	110,160
PRODUCT H	€ 1,060,012	€ 1,060,012	45,672	45,672
total:	€ 74,397,107	€ 77,821,133	1,914,542	2,047,802

So, if 1 or more value is selected in the slicer of PRODUCTS, recalculate accordingly but still be able to show me the grand total as per table above. If nothing is selected, then wathever percentage selected in my parameter should affect all data.

Is this possible?

Thanks in advance for having a look into it and if anything else or better explanation is needed, please let me know so I can help to you to help me 🙂

Best regards,

Alan.

[alan7lp]

Hi there,

I am having an issue with (I believe) something which might be easier to solve than what I think yet I can have it working.

I want to achieve the following:

I have 3 measures created in PBI: REAL PRICE, ADJUSTED PRICE (+) and ADJUSTED PRICE (-). Basically I want to replicate what this IF STATMENT in Excel is managing to do correctly (column FINAL ADJUSTED PRICE).

I tried writing the same logic in PBI but I will get either the positive adjustement or the negative one (depending on which statment I write first). For example, it will give me correct result for PRODUCT A (150) but for PRODUCT C gives me 300 (it should be 50).

Is there anyway this can be worked out?

Thanks a lot 

Cheers.

[v-yiruan-msft]

Hi @alan7lp ,

Please try the following formula for column FINAL ADJUSTED PRICE in Power BI Desktop:

FINAL ADJUSTED PRICE =
IF (
    AND (
        'PRODUCT'[ADJUSTED PRICE (+)] > 'PRODUCT'[REAL PRICE],
        'PRODUCT'[ADJUSTED PRICE (+)] > 'PRODUCT'[ADJUSTED PRICE (-)]
    ),
    'PRODUCT'[ADJUSTED PRICE (+)],
    IF (
        AND (
            'PRODUCT'[ADJUSTED PRICE (-)] < 'PRODUCT'[REAL PRICE],
            'PRODUCT'[ADJUSTED PRICE (-)] < 'PRODUCT'[ADJUSTED PRICE (+)]
        ),
        'PRODUCT'[ADJUSTED PRICE (-)],
        'PRODUCT'[REAL PRICE]
    )
)

Best Regards

Rena

[alan7lp]

Thanks @v-yiruan-msft for your reply.

I have tried that way before (as it's the same than excel for that matter) and logacally it does work but the problem in my case is that all the data you have in columns are measures. So, REAL PRICE and the ADJUSTED are built in DAX, not columns in a data table and I guess that is why it doesn't work.

But like I said, your solution is logical and would work if my data was set up as that.

Thanks again for your time.

regards

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907