Allow users of a report to visualize different curves (from different parameters selection)

[Anonymous]

Hi,

I need to make a report where the user can visualize the result of different parameters selection (on different slicers)

Here is a simple example :

I want to see on a graphic : y = ax^2 + bx + c.

I have 3 slicers : one to select a, one to select b and a last one to select c. How can I allow the user to select (1,1,1) and vizualiser the result on the graph and then keep this curve and add an other one with (2,2,2) ?

[DataZoe]

@Anonymous ,

You should be able to do this by creating two measures based on the parameters for each measure:

I created a table with a the x values from -10 to 10.

Then created a parameter for a1, b1, c1 and a2, b2, c2.

then, I create the two formulas, y1 an y2 as measures:

y1 = a1[a1 Value]*min('Axis'[x])^2+b1[b1 Value]*min('Axis'[x])+c1[c1 Value]

y2 = a2[a2 Value]*min('Axis'[x])^2+'b2'[b2 Value]*min('Axis'[x])+c2[c2 Value]

and put them on the same line chart with the x-axis.

Hope this helps!

[Anonymous]

Your solution can works but seems really time consuming to setup it (when you have 10 slicers) and probably not really easy to use. It seems there isn't better solution in power bi ?

[DataZoe]

@Anonymous 

for your specific scenarios, I think it could be done with one parameter and a duplicate disconnected table of product names.  Then you would create the measures for each scenario listed.

For example:

Sales = sum([sales])

Sales Under Input = calculate([Sales],filter(SalesTable,averagex(SalesTable,sum(SalesTable[ProductPrice]))<Parameter[Input Value]))

Sales Over Input = calculate([Sales],filter(SalesTable,averagex(SalesTable,sum(SalesTable[ProductPrice]))>=Parameter[Input Value]))

then put all the measures on a single line chart.

for the specific categories, you could use SELECTEDVALUE to create the different measures based on the input.

I'll try to mock it up later.

[Anonymous]

Yes maybe it can be a solution, I will wait your example to see exactly what you mean 🙂

[DataZoe · Accepted solution]

@Anonymous ,

Here you go:

For the left you have to create the disconnected (don't create a relationship to the other table with it) category table:

Category = SUMMARIZE('More things','More things'[Category])

and then it is used to drive the 3 measures:

Sales Total = sumx('More things','More things'[Price]*'More things'[Qty Sold])

Sales Total Selected = CALCULATE([Sales Total],filter('More things','More things'[Category]=SELECTEDVALUE(Category[Category])))

Sales Total Not Selected = [Sales Total]-[Sales Total Selected]

Then the other side is your price point slicer, which is a parameter that I created using default settings then modified to use min and max price points from the actual data:

Item Price = GENERATESERIES(min('More things'[Price]), max('More things'[Price]), 1)

Then it uses Sales Total, and these 2 measures:

Sales for Price Under Selection = CALCULATE([Sales Total],filter('More things','More things'[Price]<[Item Price Value]))

Sales for Price At or Over Selection = CALCULATE([Sales Total],filter('More things','More things'[Price]>=[Item Price Value]))

the PBIX is available here too:

https://github.com/DataZoe/PBIX/blob/master/Parameters.pbix 

[AllisonKennedy]

How many curves do you want to be able to display at a time? You may need to consider having separate parameters for each curve.

[Anonymous]

2 or 3 curves

I took a simple example but this is not just numbers or coefficients. It's about textual parameter, number, etc...

[AllisonKennedy]

Most straightforward way I can think of would be to create 2 or 3 sets of parameters, for each curve, so in your simple example above it would be 9 total parameters that the user could update and interact with to change the whatif analysis in the chart.

Then it's just a matter of building the measures to use those parameters.

[Anonymous]

Yes but how can you vizualize 2 curves with different selections on the same graph ? (at the same time btw)

[AllisonKennedy]

Depends which graph you're using I guess, but just create a new MEASURE for the right side of the equation for y = for each curve, and put this in the VALUES of the graph, or alternatively solve the equation for X and put that in the MEASURE formula and put that in the VALUES of the graph. You can put as many measures in the values as you need, so 2 or 3 will be no problem.

Does that make sense/help?

[Anonymous]

Hi, Thanks for your answer but I dont think it solves my problem.

Here is an example more realistic :

I want to vizualize the sales volume on the last 12 months. Imagine you have the value (sales volume) and at least 2 slicers : pricing and category of items. The user wants on the same graphic :

• the global sales volume
• the sales volume for item under 10$ (i choose here 10$ but maybe the user will try 12 or 8 or anything else)
• the sales volume for item above 10$

Then maybe the user will want to vizualize :

• the global sales volume (like in the previous graphic)
• the sales volume for "high tech" item
• the sales volume for "non high tech" item (it means selecting all category item except "high tech")

How can I setup this kind of feature in a report ? I cant believe it's not possible in a software like power bi ?