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

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.