Multiplication of Measure and column (per distinctive row)

[Anonymous]

Hey,

THis can sound as complex problem, so I'll try my best do describe the problem.

I have a table which contains two columns which I need to plot in a scatter plot.

For this scatter, I've calculated the slope of the trendline in DAX (using this https://xxlbi.com/blog/simple-linear-regression-in-dax/).

This slope is a Measure in my table and I can return it as a value in a card visual.

What I now want to do, is multiply this slope with 1, 5 and 10. This sounds easy but I would like to integrate the choice of 1, 5 and 10 in a slicer (dropdown list). For that, I've pivotted my dataset so that for every row in the initial table, I now have three rows (for 1, 5 and 10), this because I can then integrate the slicer with based on the column 'Effect' which contains 1,5 and 10.

Here, the problem arises: now I need to multiply the column 'Effect' with the slope (which is a measure). This doesn't seem to work as I see it, although not per row.

Hope this is clear!

[TomMartens]

Hey @Anonymous ,

please create a pbix that contains sample data but still reflects your data model, upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom

[Anonymous]

Hi Tom,

I did, thanks!

https://www.dropbox.com/s/y9kozh7ggbrb42c/SampleDataForum.pbix?dl=0

Additional information, the measure 'Slope' is the slope of the trendline of the graph.

As slope tells information about how Y increases/decreases (in my case, decreases) when X increases/decreases (in my case, increase with 1, 5 or 10), I want to show this effect in a card and select the increase in X (1, 5 or 10) in a slicer.

PS: I'm aware that the increase should in fact be 0,01, 0,05 and 0,1 as the X-axis is in percentage but to make it easier, let's assume 1, 5 or 10)

[TomMartens]

Hey @Anonymous ,

maybe this is what you are looking for:

sumx(
    'Sample data'
    , 'Sample data'[Score] * [Score]
)

Regards,

Tom

Stay safe, stay healthy, and happy holidays

[bheepatel · Accepted solution]

Hi @Anonymous 

I would suggest the use of a disconnected table. Create a new table with just 1 column as per the table below.

Options

1

5

10

You can then place the 'Options' field from the above table in a slicer like you want to.

There is a DAX function called SELECTEDVALUE().

In the last line on the measure for the Slope, you can simply say:
RETURN Slope * SELECTEDVALUE('Options')

[Anonymous]

Hi @bheepatel,

This did the trick, thanks for this.

But additionally, would it be possible to now make a calculation between that effect (so slope*SelectedValue (1,5 or 10)) and the spend column. In this way, I can display the how much the spend dropped when the score drops with 1, 5 or 10. This basically means a calculation between a column and a measure, which seems not to be possible.

[bheepatel]

Hi @Anonymous 

I think it would be possible. You will just have to create another measure where you use the spend column and then the result of the Slope measure. I am not exactly sure what your formula is or what you intend on calculating but for example you can use the following:

New Measure = 'Table'[Spend] * [Slope]

You can then place this New Measure in the visual that you want to.

[Anonymous]

Hi,

New model: https://www.dropbox.com/s/l0qt4geecs5uzoo/SampleDataForum.pbix?dl=0

What I basically want is to substract the effect (measure) (so slope*1,5 or 10) from the spend (column) so it shows the 'New' Spend when an effect on Score is taken.

[bheepatel]

You can use the following measure to determine the reduction of spend:

Reduction = SUMX('Sample data', 'Sample data'[Spend (EUR)] * [Effect])