cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Multiply column values by a number which can be altered on the dashboard

Hi

 

I want to multiply the cost of multiple items by a mark up value.  I can do this for a predetermined fixed number by creating a simply calculated column either in the query editor or in the data view.  For example:

 

New Column = 'Project Hours Booked'[Cost, Reg.] * 2.2

 

However, the people viewing the dash board (i.e. senior management) that I have created using this column would like to be able to adjust this mark up value without having without having to go back to the data set or the query editor.   Is there a way to put a filter or text field on the dashboard which when edited updates the calculation?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Multiply column values by a number which can be altered on the dashboard

If it is going to be dynamic, then you will need to use a measure. Create a table of your potential markup values. Use that as your slicer. Then, create a measure like this:

 

Measure = 'Project Hours Booked'[Cost, Reg.] * MAX([MarkupColumn])

Changing the slicer will change the MAX value of your MarkupColumn (filter to just that one value) and your measure will calculate the markup on the item.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Highlighted
Super User IX
Super User IX

Re: Multiply column values by a number which can be altered on the dashboard

If it is going to be dynamic, then you will need to use a measure. Create a table of your potential markup values. Use that as your slicer. Then, create a measure like this:

 

Measure = 'Project Hours Booked'[Cost, Reg.] * MAX([MarkupColumn])

Changing the slicer will change the MAX value of your MarkupColumn (filter to just that one value) and your measure will calculate the markup on the item.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Highlighted
Helper I
Helper I

Re: Multiply column values by a number which can be altered on the dashboard

Thanks, appreciate the quick reply!

 

One last question on this if you don't mind.  

 

So in order to create the slicer, I was going to create a new table with a list of numbers starting at 2.00 and ending in 2.40 (essentially 41 numbers).  Obviously I can enter the values manually but I was hoping to be able to generate it using a DAX formula.  Can you help?

Super User IX
Super User IX

Re: Multiply column values by a number which can be altered on the dashboard

I would just create it in Excel and copy and paste into an Enter Data query.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors