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
shubh_kush
Helper I
Helper I

Dynamic Sum

Hi Everyone, 

After tons of googling and RnD, finally its time to ask you guys this query 🙂

 

My PowerBI report has two columns say "Column_1", which has its dedicated slicer "Slicer_1" and "Column_2", which again has its dedicated slicer "Slicer_2".

 

Column_1 and Column_2 is Measure type and has Cost related data.

Slicer_1 and Slicer_2 has percentage data in the from of Dropdown: 5, 10, 15, 20, 25, 30.....till 95.

 

When I select 20 in Slicer_1, then 20% of cost in Column_1 is calculated, say the result is $55,000  

When I select 25 in Slicer_2, then 25% of cost in Column_2 is calculated, say the result is  $25,000   

Now I want to have a third column "Total_Cost", which evaluates the Total Dynamic Sum. 

That is, Total_Cost  = Column_1 + Column_2

            Total_Cost = $55,000 + $25,000 

            Total_Cost = $80,000

What I want to achieve, if user selects some other percentage in any of the slicer, the Total Dynamic Sum in Total_Cost must change.

 

Thanks in Advance.

Stay Safe !

 

 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @shubh_kush 

According to your description, you can follow my steps:

  1. Create two what-if parameter for Slicer1 and Slicer2, like this:

v-robertq-msft_0-1604655792447.png

 

  1. Create a measure [Total_Cost]:
Total_Cost =

var _column1=[Column_1]*[Slicer1 Value]*0.01

var _column2=[Column_2]*[Slicer2 Value]*0.01

return _column1+_column2

 

  1. Create Card visual for each measure, like this:

v-robertq-msft_1-1604655792468.png

 

And you can get what you want.

You can download my test pbix file here

 

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-robertq-msft
Community Support
Community Support

Hi, @shubh_kush 

According to your description, you can follow my steps:

  1. Create two what-if parameter for Slicer1 and Slicer2, like this:

v-robertq-msft_0-1604655792447.png

 

  1. Create a measure [Total_Cost]:
Total_Cost =

var _column1=[Column_1]*[Slicer1 Value]*0.01

var _column2=[Column_2]*[Slicer2 Value]*0.01

return _column1+_column2

 

  1. Create Card visual for each measure, like this:

v-robertq-msft_1-1604655792468.png

 

And you can get what you want.

You can download my test pbix file here

 

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@shubh_kush , something like this

sum(Table[Column_1])*selectedvalue(Slicer_1[value]) + sum(Table[Column_2]) * selectedvalue(Slicer_2[value])

 

Assuming slicers are what if measure - https://docs.microsoft.com/en-us/power-bi/desktop-what-if

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.