dynamic value based on slicer

[Anonymous]

Hi community,

I've got a question regarding a % multiplier that I'm trying to create in Power BI:

I have a matrix table of values here which I multiply via my %slicer. This part works fine across all categories, however I want to be able to add multiplier to the right category, based on a second slicer.

The codes that I'm using are as follows:

Test multiplier = SELECTEDVALUE('Scenario Selector Rate'[Scenario multiplier])

Soll Inventory/AR/AP = [Inventory/AR/AP incl. VAT]+([Inventory/AR/AP incl. VAT]*[Test multiplier]))

Any help would be much appreciated!

[Anonymous]

// I assume that [soll inventory/ar/ap] must be
// summable across all the flows. If not, then
// some logic must be implemented to return
// values at the right level of granularity.
// T is the table where you store flows. I understand
// as well that 'Detailed Flow' is disconnected
// from the model.

[soll inventory/ar/ap] =
var __multiplier = coalesce([test multiplier], 0)
var __flowsToModify = VALUES( 'Detailed Flow'[Flow] )
var __result =
    SUMX(
        VALUES( T[Flow] ),   
        var __shouldModifyCurrentFlow =
            T[Flow] in __flowsToModify
        var __effectiveMultiplier = 
            __shouldModifyCurrentFlow * __multiplier
        var __invIncVat = [inventory/ar/ap incl.VAT]
        return
            (1 + __effectiveMultiplier ) * __invIncVat
    )
return
    __result

[Anonymous]

Hi Daxer,

First of all thanks for your help, I tried to implement your logic but I ran into the following conversion error:

Since the CONTAINSROWS function wasn't used I started to debug. I noticed that the __flowsToMatch variable returned an error for me when I selected multiple values:

This is unfortunate as I'm looking specifically for that multiple selection but even when I select a single value, I still get the original conversion error. I've been able to isolate it to the var __result but that's a far as I've come.

[Anonymous]

Well, CONTAINSROW is the function used under the hood when you use A IN B. Your problem stems from the fact that you're trying to compare text with currencies. Please make sure your types are consistent across the model. Please check out the first error message.

[Anonymous]

Please always remember that you should never store numbers (with the semantics of numbers) as text. If you have a need to show numbers specifically formatted, please use the formatting option on the ribbon.

[Anonymous · Accepted solution]

Hi Daxer,

Thanks again for your help. I was able to solve my issue in the following manner:

First if all, I created the following measure to filter out the specific categories I want:

Selectable Soll Inv/AR/AP =

CALCULATE(

SUM(ProfitLossData[Inventory/AR/AP])+

(SUM(ProfitLossData[Inventory/AR/AP])*[Soll Multiplier Inv/AR/AP])

,

ProfitLossData[Detailed Flow] IN(

ALLSELECTED('Area Slicer'[Simulated Areas])

)

)

After creating this, I used an IF statement to determine which values I should take for the result column:

IF Statement =

IF( ISBLANK([Selectable Soll Inv/AR/AP]),

SUM(ProfitLossData[Inventory/AR/AP]),

[Selectable Soll Inv/AR/AP])

Thanks again for all the help!

[Anonymous]

Yeah... but your measure does not look to be summable across categories. My measure is OK but you must have changed it somehow when you were adjusting it to your model.

[amitchandak]

@Anonymous , try like Assumed the other slicer -'Table'[Detailed Flow]

Soll Inventory/AR/AP = if(max('Table'[Detailed Flow]) =SELECTEDVALUE('Table'[Detailed Flow]) , [Inventory/AR/AP incl. VAT]+([Inventory/AR/AP incl. VAT]*[Test multiplier]) , [Inventory/AR/AP incl. VAT])

Soll Inventory/AR/AP = if(isfiltered('Table'[Detailed Flow]) && max('Table'[Detailed Flow]) =SELECTEDVALUE('Table'[Detailed Flow]) , [Inventory/AR/AP incl. VAT]+([Inventory/AR/AP incl. VAT]*[Test multiplier]) , [Inventory/AR/AP incl. VAT])