how to apply field to Small Multiple or Column Series under this situation?

[h_l]

Hi Senior Users,

Could you please help on following scenario:

We would like to get the Performance by Department on the basis of their focused "Key Queries".

In attached file has 2 sample charts with sample data. Please click there to download the sample PBI file.

For example: following is the overall performance, we want to make the "Department" to be Small Multiples.

In the data, there are tables like:

It’s little complex to me, as single “Query” may contain more than 1 focused "Key Queries" belongs to different department.

For example: "TESLA Premier download", contains Key Queries belong "Software" and "Service" Department, so, after applying "Department" to Small Multiples or Colmn Series, it shall be included in both charts / columns.

Hopefully the chart can be drilled down / up by date hierarchy as well (in other cases, I see some times drill up / down will not work)

Thank you and have safe day!

H

[lbendlin · Accepted solution]

@h_l See attached. It took a bit of reorganizing your data model to achieve the goal.  You may want to shift that to the data acquisition phase. With cleaner source data the visualizations become easier too.

[h_l]

Hi @lbendlin , sorry for the late repy. I just downloaded and look into it, if my understanding is not wrong, the method is to "copy" another row if the search term contains more than 1 keywords contained in more than 1 department.

I much appreciate your help.

Have a joyful day!