Calculated column based on the number entered in a slicer

[ashishoza12345]

Hi Folks,

I need your ugent help on the current project

So, basically, I have given below table in PowerBI:

Now here I would like to create a calculated column "New_Change" where I enter a value for each "Continent" in a slicer & "Cap Value" in a slicer as shown below, it would create a new calculated column ("New_Change")  where score value is calculated by the xx% (manually entered in a slicer) on a PowerBI page but it is not greater than "Cap Value" (manually entered in a slicer) .

The End table would look like this based on the values entered manually on a PowerBI page:

Thanks in advance! 

[MFelix · Accepted solution]

Hi @ashishoza12345 ,

You cannot have values on a table being influenced by a slicer for this you need a measure, create parameters for each of the values of your selection and the add the following metric:

New Change = 
VAR _NewChange =
    SELECTEDVALUE ( 'Table (2)'[Score] )
        * SWITCH (
            SELECTEDVALUE ( 'Table (2)'[Continent] ),
            "Africa", [Africa Value],
            "Asia", [Asia Value],
            "North America", [North America Value],
            "Europe", [Europe Value]
        )
RETURN
    IF ( _NewChange <= 30, _NewChange, 30 )

[MFelix · Accepted solution]

Hi @ashishoza12345 ,

Here is the file I have also change the metric a little bit so that the cap is dynamic based on the slicer wich I have forgotten in the previous version:

New Change = 
VAR _NewChange =
    SELECTEDVALUE ( 'Table'[Score] )
        * SWITCH (
            SELECTEDVALUE ( 'Table'[Continent] ),
            "Africa", [Africa Value],
            "Asia", [Asia Value],
            "North America", [North America Value],
            "Europe", [Europe Value]
        )
RETURN
    IF ( _NewChange <= [CAP Value Value], _NewChange,[CAP Value Value] )

[ashishoza12345]

Hi @MFelix ,

That's Awesome! Thank you so much for our kind help! 

Kind Regards,

Ashish Oza.

[ashishoza12345]

Hi @MFelix ,

Thank you so much for your valuable time and providning me this solution.
Really appreciate your effort! 🙂

Once again Thank you! 🙂

Kind Regards,

Ashish Oza.

[MFelix · Accepted solution]

Hi @ashishoza12345 ,

You cannot have values on a table being influenced by a slicer for this you need a measure, create parameters for each of the values of your selection and the add the following metric:

New Change = 
VAR _NewChange =
    SELECTEDVALUE ( 'Table (2)'[Score] )
        * SWITCH (
            SELECTEDVALUE ( 'Table (2)'[Continent] ),
            "Africa", [Africa Value],
            "Asia", [Asia Value],
            "North America", [North America Value],
            "Europe", [Europe Value]
        )
RETURN
    IF ( _NewChange <= 30, _NewChange, 30 )

[ashishoza12345]

Hi @MFelix ,
Can you please send me the PowerBI file for this solution?

[MFelix · Accepted solution]

Hi @ashishoza12345 ,

Here is the file I have also change the metric a little bit so that the cap is dynamic based on the slicer wich I have forgotten in the previous version:

New Change = 
VAR _NewChange =
    SELECTEDVALUE ( 'Table'[Score] )
        * SWITCH (
            SELECTEDVALUE ( 'Table'[Continent] ),
            "Africa", [Africa Value],
            "Asia", [Asia Value],
            "North America", [North America Value],
            "Europe", [Europe Value]
        )
RETURN
    IF ( _NewChange <= [CAP Value Value], _NewChange,[CAP Value Value] )

[ashishoza12345]

Hi @MFelix ,

Thank you so much for your kind response.

Actually, there has been a small change in the dataset where my PBI users would like to see aggregated figures on the PBI Page.

For Example, how do we check the continent numbers, if we have One more field City is added into the dataset: 
As shown below, if Cap Value is 100 & Change % for Africa & Asia is 20% & 30% respectively, then in the background that cap value is applied on each row and on the PBI Page we get to see the total of each continent where each City "Change" is not more than 100.

Based on using Cap Value as a filter "Change" field gets updated

On the PowerBI page we get like given below visual:

I have also shared the PBI link below:
https://drive.google.com/drive/folders/1S0yeeVEJyOeY8200Ct9z9o7zXccmbtuS?usp=sharing

Please let me know if you need any additional information.
Your help in this regard is highly apprecaited.

Kind Regards,

Ashish Oza.

[MFelix · Accepted solution]

Hi @ashishoza12345 ,

On top of the metric you already have add the following measure:

Aggregated total =
SUMX (
    SUMMARIZE (
        'Sample',
        'Sample'[Continent],
        'Sample'[Country],
        'Sample'[City],
        "NewChangeValue", [New Change]
    ),
    [NewChangeValue]
)