Comparative chart with various dynamic measures to choose from

[FelixZM]

Hi, All,

I need help with the following case that I need to solve (if it is possible).

Suppose we have a data table like the following:

Company	Plant	Date	amount sale	amount purchase	quantity objects
001	001	Monday, October 17, 2022	150	130	1
001	002	Sunday, October 16, 2022	123	55	2
001	001	Sunday, October 16, 2022	84	14	6
001	003	Saturday, October 15, 2022	69	60	1
002	001	Monday, October 17, 2022	148	135	1
002	002	Sunday, October 16, 2022	2000	50	40
002	001	Sunday, October 16, 2022	16	14	1
002	003	Saturday, October 15, 2022	199	26	7

I need a chart with the date on the x-axis to be able to add any of the 3 variables (it must be possible to select one or more) which in turn can be compared between companies and plants.

I have tried to create a separate table with the measures that the user can select, but when adding several measures in the graph, I can not include as legend the values of company or plant.

Another option I have tried is to transpose the table, so that the columns with amounts are user selectable and the "value" column as y-axis. In this way, I can make the comparison, but, if I select several variables, it accumulates them (so I cannot compare them).

Also, I cannot use the "small multiples" option.

Any idea how I can solve this?

Thank you!!

[FelixZM]

Hi, @DataInsights,

Thank you for your reply! But I have two problems with this:

1 - This is a public preview and I can't use it in production enviroments.

2 - I can only use the version 2.97 of Power BI Desktop.

Thanks!

[DataInsights]

@FelixZM,

Another option is to create a calculation group, where each calculation item is a measure. Create a slicer using the calculation group, and users will be able to select the measure(s) they want to display. You'll need to install Tabular Editor to create a calculation group.

https://www.sqlbi.com/calculation-groups/ 

[DataInsights]

@FelixZM,

You could try a fields parameter. Add each measure to the parameter, and it will create a slicer that allows users to choose which measures to display.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters 

[FelixZM · Accepted solution]

Finally, I have solved the problem, performing an unpivot of the "Amount Purchase", "Amount Sales" and "Quantity objects" columns.

and concatenating the Company, Plant and the new unpivot column to add it in the "Legend" section of the line chart.

Thank you!