3 Queries - Dynamic Axis Label | Curved Trendline | Axis Label formatting

[RockandGrohl]

Hello all,

I'm hoping someone can help me with these two issues, I'm fairly new to PowerBI and am struggling here with some stuff that feels like it should be basic functionality. All these queries revolve around a scatter chart which is being fed by a "Database" table.

1) I have a chart where I'm measuring the cost rate on the Y axis against a "Quantity" on the X axis. Our database has many lines with products on them, and the all the measurements are in the Quant1 column as a number. However, this can be like square meters, volume, meteres length, number of units. So it's always a number. The name of the unit of measurement is on the same row in the "Primary Unit of Measure" column.
What I'd like to do is have the X-axis label change depending on what product is selected on the scatter chart. For instance, if I select a path repair as the product, the X-axis label changes to say "Meters (length)".

2) The default option in Power BI for a Trendline on a scatter chart is a linear line. It would be better to have a curved line as you can do in Excel to account for density of scatter points on certain areas of the chart. Is this possible?

3) This is a fun one. My "Rate" column is set as a cost per unit (cost per meter cubed, cost per meter square, cost per meter length) etc. However, this column also has percentages in it. I have a second chart on another tab which uses the same axis. Rate on the Y-axis and Quant on the X-axis. But this time, the Quant is an overall £cost and the rate is a percentage. I can't add more helper columns etc. This change in formats occurs when the "Primary Unit of Measure" column contains "Construction Costs". Essentially all I want to do is to format this second column with percentages on the Y axis, and currency on the X-axis.


I've put some example data in the table below:

Asset	Cost	Type	Primary UoM	Quant1	Rate	Explanation
Wall	£9,000	Direct	m2	30	£300	£9k cost divided by 30 square meters = £300 rate.
Site compound	£18,000	Indirect	Construction Costs	£60,000	0.3	£18k cost divided by £60k overall project cost = 30% of project cost
Pathway	£20,000	Direct	meter (length)	500	£40	20k/500 meters = £40/meter
Access Track	£50,000	Indirect	Construction Costs	£200,000	0.25	Cost of this asset is 25% of overall project cost

So there's two charts. One for Direct Costs, and another for Indirect Costs.

The direct costs chart will have the Rate on the Y-axis, Quant on the X-axis. The X-axis label needs to reflect the Primary UoM. So if "Wall" is selected on the scatter chart, it would show all the Wall lines, and the "Quant1" label would change to reflect the Primary UoM, in this case "m2"

The indirect costs chart is pre-filtered to only show assets with Construction Costs as the Primary UoM. The chart set up is the same - Rate on the Y-axis, Quant 1 on the X. But I just want the format changed so that Y is % and X is currency.

Thank you!

 

[RockandGrohl]

This is what I want - a logarithmic trendline, where the data points are linear (not log)

Excel allows this, but PowerBI doesn't.

 

[RockandGrohl]

I got the Axis label working. When there's two values in the lookups, instead of giving me the first, it just gives me an error.

I'm going to expand it so that the lookup is based on three filters applied to the BI table.. Hopefully will be easy to do. Here's my measure.

SelectedAssetPrimaryUoM = 
CALCULATE(
    VALUES('Database'[Primary UoM]),
    'Database'[Asset] = SELECTEDVALUE('Database'[Asset]))