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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
dyrcm
Frequent Visitor

Choose a number in a list to multiply against a column

I have a table with a column for Actualy Monthly Revenue and a calculated Row for Annual Revenue.  I would like to give my user an option to click on button with 10%, 20%, 30%, etc and the Revenue columns to be multiplied times this percentage.  

 

I am new to Power BI so be gentle. 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @dyrcm,
In one of my dashboard I add to have some simulations run at different percentages and I was abble to do it using a separate table and making the calculation based on that table using a slicer, this is similar to the approach that I use to make YTD, QTD, and MTD calculation (check also http://community.powerbi.com/t5/Desktop/Switching-Values-on-a-report-with-a-splicer/td-p/73389)


First create a table with the parameters you want to use for your slicer that as the following structure:
Table Name = Simulation

Increment
0,1
0,2
0,3
...

Since this is percentages and you want to multiply this by the values it's easier to do it with decimal values.

IMPORTANT: dont' create any relationship with other tables must be a standalone table.

Within the table create the two following measures:
Selected Timeframe= MIN(Simulation[Increment])
Selection = IF (HASONEVALUE ( Simulation[Increment]), VALUES ( Simulation[Increment]))


The first measure gives you the minimum Increment selected in the slicer.
The second one gives tells you what is the interval to consider when you select the values in the slicer if none are selected the full table is considered and then the minimum value is used based on the first measure.

On the table where you have the Revenue create the following measures:

Actualy Monthly Revenue Recalc = Actualy Monthly Revenue * ALLSELECTED(Simulation[Increment])

Now you can use the Increment for your slicer and the Actualy Monthly Revenue Recalc for you graphs, tables whatever when you change the slicer the calculations change accordingly.

Regarding the annual value this can be made by the sum function in your visuals.

Hope this helps. Any question please tell me.

MFelix


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

1 REPLY 1
MFelix
Super User
Super User

Hi @dyrcm,
In one of my dashboard I add to have some simulations run at different percentages and I was abble to do it using a separate table and making the calculation based on that table using a slicer, this is similar to the approach that I use to make YTD, QTD, and MTD calculation (check also http://community.powerbi.com/t5/Desktop/Switching-Values-on-a-report-with-a-splicer/td-p/73389)


First create a table with the parameters you want to use for your slicer that as the following structure:
Table Name = Simulation

Increment
0,1
0,2
0,3
...

Since this is percentages and you want to multiply this by the values it's easier to do it with decimal values.

IMPORTANT: dont' create any relationship with other tables must be a standalone table.

Within the table create the two following measures:
Selected Timeframe= MIN(Simulation[Increment])
Selection = IF (HASONEVALUE ( Simulation[Increment]), VALUES ( Simulation[Increment]))


The first measure gives you the minimum Increment selected in the slicer.
The second one gives tells you what is the interval to consider when you select the values in the slicer if none are selected the full table is considered and then the minimum value is used based on the first measure.

On the table where you have the Revenue create the following measures:

Actualy Monthly Revenue Recalc = Actualy Monthly Revenue * ALLSELECTED(Simulation[Increment])

Now you can use the Increment for your slicer and the Actualy Monthly Revenue Recalc for you graphs, tables whatever when you change the slicer the calculations change accordingly.

Regarding the annual value this can be made by the sum function in your visuals.

Hope this helps. Any question please tell me.

MFelix


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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.