Forum Discussion
Helper IIHow to compare two values from different perspectives?
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.
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.
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.
7 Replies
- lbendlin
Super User
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.
- lbendlin
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
Community Support
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.
- littlemojopuppy
Community Champion
lcfarialittlemojopuppy, 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.
