How to compare two values from different perspectives?

[lcfaria]

I'm looking to compare the results of my managers, platform and unit. I am attaching the pbix example I need, this is still not working the way I need it.

What I would like was to be able to compare, for example:
the average of the manager with the average of the platform or the average of the unit with the average of the manager, selecting what I want to see from a slicer, for example.

The manager information is in my fact table (I don't have a dimension for that yet), the platform and the unit come from a dimension table.

I would like to be able to choose what I want to see on my chart, being able to compare the manager, platform or unit averages.

The way I have it today, I can compare manager with manager or platform with platform, but not manager with platform, like image below:

Here is the link to this file: https://1drv.ms/u/s!At5JlMR_naI1gf4vug6wRFZ4bI1MCw?e=0fF7fN

I hope I managed to explain my question.

[lbendlin · Accepted solution]

You would be looking at a monster like this

UNION(
SELECTCOLUMNS(SUMMARIZECOLUMNS('Calendar'[year week], 'Fact'[Manager], "Average", 'Fact'[Average]),"Week",[year week],"Average",[Average],"Attribute","Manager","Value",[Manager]),
SELECTCOLUMNS(SUMMARIZECOLUMNS('Calendar'[year week], 'Table_1'[Platform], "Average", 'Fact'[Average]),"Week",[year week],"Average",[Average],"Attribute","Platform","Value",[Platform]),
SELECTCOLUMNS(SUMMARIZECOLUMNS('Calendar'[year week], 'Table_1'[Unit], "Average", 'Fact'[Average]),"Week",[year week],"Average",[Average],"Attribute","Unit","Value",[Unit])
)

which then results in a visual like this

see attached.

[Icey · Accepted solution]

Hi @lcfaria ,

Please check if this blog could help you: Dynamic EVERYTHING (measures, axis, legend, titles... - Microsoft Power BI Community.

This blog shows layered dynamic DAX measures, i.e. how one chart can show different values, in different time frames, different scale. Report also shows how a slicer can be used to dynamically change axis and legend. Report has dynamic title and switch for chart types. Database layout is quite simple (and can be used for other cases) but uses a bit of trickery to create such dynamic solution using slicers to change axis/legend. 

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

If you really want to do that you will have to duplicate your fact table and then create independent measures for each of the cuts you want to do. 

I would be really worried about the enormous maintenance effort and about confusing my users that way but YMMV.

[lcfaria]

@lbendlin, but do you think it would be possible to do that, despite all the effort?

I didn't understand how exactly I can build the measures to get to this result.

[lbendlin · Accepted solution]

You would be looking at a monster like this

UNION(
SELECTCOLUMNS(SUMMARIZECOLUMNS('Calendar'[year week], 'Fact'[Manager], "Average", 'Fact'[Average]),"Week",[year week],"Average",[Average],"Attribute","Manager","Value",[Manager]),
SELECTCOLUMNS(SUMMARIZECOLUMNS('Calendar'[year week], 'Table_1'[Platform], "Average", 'Fact'[Average]),"Week",[year week],"Average",[Average],"Attribute","Platform","Value",[Platform]),
SELECTCOLUMNS(SUMMARIZECOLUMNS('Calendar'[year week], 'Table_1'[Unit], "Average", 'Fact'[Average]),"Week",[year week],"Average",[Average],"Attribute","Unit","Value",[Unit])
)

which then results in a visual like this

see attached.

[lcfaria]

@lbendlin, I did some changes in your code and it works fine for me. Thank you for your help!

[littlemojopuppy]

Hi @lcfaria 

Question: are managers assigned to/members of a single platform and unit?

[lcfaria]

@littlemojopuppy, no, managers are not assigned to a platform or unit, but I would like to compare them with the average of platforms and units, if possible.