HI there,
Had a query regards to combo chart functionality.
Scenario:
Sample DataSet :
|
Dataset |
|
|
|
|
Year |
Client |
Region |
Country |
Industry |
Cost |
2022 |
Google |
Europe |
France |
Tech |
10 |
2022 |
Amazon |
USC |
US |
E com |
20 |
2022 |
Flipkart |
Asia |
India |
Hardware |
30 |
2022 |
Google |
Europe |
France |
Tech |
22 |
2022 |
Amazon |
Europe |
US |
E com |
33 |
2022 |
Flipkart |
USC |
India |
Hardware |
40 |
2023 |
Google |
Europe |
France |
Tech |
12 |
2023 |
Amazon |
Asia |
US |
E com |
20 |
2023 |
Flipkart |
Asia |
India |
Hardware |
30 |
2023 |
Google |
Europe |
France |
Tech |
35 |
2023 |
Amazon |
Asia |
US |
E com |
20 |
2023 |
Flipkart |
Asia |
India |
Hardware |
15 |
Expectation is to show 2 bars for each year, with Average cost by region/country/industry. And 1 line with the average cost for a selected client from a filter.
When we would select a client, the chart should show average cost of all the clients in the region /country/industry the client is present in only.
I had some success with the bar measure for one dimension :
*Prev year cost for Region dimension:
avg({<Client = , Region = p({<Client = {'$(=GetFieldSelections(Client))'}>}Region), Year={'$(=Year(Today())-1)'}>}) Cost )
1. Can the formula be modified to accomodate multiple selections? Currently as soon as I select one more client, the formula breaks.
2. Is there a better way to fulfil the above requirement