Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Calculated column based on the number entered in a slicer

Hi Folks,

I need your ugent help on the current project

 

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

ashishoza12345_0-1680512717867.png

 

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) .

 

ashishoza12345_2-1680513736817.png

 

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

 

ashishoza12345_3-1680514161280.png

 

 

Thanks in advance! 

 

 

 

 

 

 

 



3 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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_0-1681117894872.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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] )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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]
)

MFelix_0-1682324241364.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7

Hi @MFelix ,

 

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

 

 

Kind Regards,

Ashish Oza.

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
Super User
Super User

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_0-1681117894872.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ,
Can you please send me the PowerBI file for this 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] )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

 

ashishoza12345_0-1682299150336.png

 

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

ashishoza12345_1-1682299221840.png

 

On the PowerBI page we get like given below visual:

ashishoza12345_2-1682299254418.png

 

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.

 

 

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]
)

MFelix_0-1682324241364.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.