Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Community
I have the following plot designed with Box and Whisker Visual by MAQ Software @MAQSupport:
The image shows the representation I would like to achieve. The ComparisonGroup Category contains 2 Values: "Focus" and "Peers":
Currently the evalution whether a Company is in category "Focus" or "Peers" happens through a calculated column:
ComparisonGroup = SWITCH(true(); Company[Company] = VALUES('Focus Company'[Focus Company]);"Focus"; Company[Company] <> VALUES('Focus Company'[Focus Company]);"Peer" )
As a calculated column is not very dynamic I am unable to change the focus/peer selection through filter elements in a way that the Box and Whisker Plot is updated immediately. If I want to change the Focus company I would have to:
'Focus Company'[Focus Company]to a different company
Instead I would like to implement it in a dynamic way to change the Axis category through a simple filter selection as follows:
A changing filter selection should change the Axis category in the Box and Whisker plot accordingly.
I am not sure whether this needs the ability to put a measure in Axis category field or if there is other DAX magic to achieve what I want in a dynamic fashion.
Thanks for your help,
Sven
Solved! Go to Solution.
I think I finally found a solution so that I am able to dynamically change the values represented on the axis category. To help you understand how it works I would like to present the corresponding values/fields in a table structure first:
The company peer selection table represents the companies I would like to summarize to compare to the selected focus company:
It's a table without any relationship to other tables.
Then there is a CompanyWithPeer table which contains all companies with an additional "Peers" entry:
CompanyWithPeer = UNION(DISTINCT(Company[Company]); ROW("Peers"; "Peers") )
It's again a table without any relationship to other tables.
The tricky part comes with the corresponding measure you need to create:
Focus vs. Peers = VAR TotalRevenueGrowthRateFocusCompany = [KPI Value Selection] VAR TotalRevenueGrowthRatePeerCompanies = CALCULATE([KPI Value Selection]; INTERSECT(ALL(Company[Company]); VALUES('Company Peer Selection'[Company]) ) ) RETURN IF(HASONEVALUE(CompanyWithPeer[Company]); SWITCH(VALUES(CompanyWithPeer[Company]); "Peers"; TotalRevenueGrowthRatePeerCompanies; VALUES(Company[Company]); TotalRevenueGrowthRateFocusCompany; BLANK() ); [KPI Value Selection] )
In the visual the columns and the measure are used as follows:
which leads to the following representation:
To better understand what's happening in the background I have split up the values used in a matrix visual:
If we show additionally the columns with no data ("Show items with no data" context action) it becomes more clear what's happening:
We are now able to select different focus / peer companies and the visual adapts immediately
I think I finally found a solution so that I am able to dynamically change the values represented on the axis category. To help you understand how it works I would like to present the corresponding values/fields in a table structure first:
The company peer selection table represents the companies I would like to summarize to compare to the selected focus company:
It's a table without any relationship to other tables.
Then there is a CompanyWithPeer table which contains all companies with an additional "Peers" entry:
CompanyWithPeer = UNION(DISTINCT(Company[Company]); ROW("Peers"; "Peers") )
It's again a table without any relationship to other tables.
The tricky part comes with the corresponding measure you need to create:
Focus vs. Peers = VAR TotalRevenueGrowthRateFocusCompany = [KPI Value Selection] VAR TotalRevenueGrowthRatePeerCompanies = CALCULATE([KPI Value Selection]; INTERSECT(ALL(Company[Company]); VALUES('Company Peer Selection'[Company]) ) ) RETURN IF(HASONEVALUE(CompanyWithPeer[Company]); SWITCH(VALUES(CompanyWithPeer[Company]); "Peers"; TotalRevenueGrowthRatePeerCompanies; VALUES(Company[Company]); TotalRevenueGrowthRateFocusCompany; BLANK() ); [KPI Value Selection] )
In the visual the columns and the measure are used as follows:
which leads to the following representation:
To better understand what's happening in the background I have split up the values used in a matrix visual:
If we show additionally the columns with no data ("Show items with no data" context action) it becomes more clear what's happening:
We are now able to select different focus / peer companies and the visual adapts immediately
Hello @Anonymous,
Due to the design limitations of Box and Whisker by MAQ Software, we cannot add support for measures in Axis category.
Thanks.
Hi Elektryon,
According to your description, you want to achieve the result based off your multiple selections in a slicer, right? If it is, you should create a measure, not a calculate column using DAX formula like pattern below:
ComparisonGroup = SWITCH ( TRUE (); Company[Company] = CALCULATE ( VALUES ( 'Focus Company'[Focus Company] ); ALLSELECTED ( 'Focus Company' ) ); "Focus"; Company[Company] <> CALCULATE ( VALUES ( 'Focus Company'[Focus Company] ); ALLSELECTED ( 'Focus Company' ) ); "Peer" )
Regards,
Jimmy Tao
Hi Jimmy
Thanks a lot for your time and your answer. Unfortunately, it seems the Box and Whisker visual does not accept a measure for its axis category. Which is why I started out with a calculated column in the first place.
Thanks,
Sven
Hi Elektryon,
In this senario, I'm afraid you should consider to use other visuals instead.
Regards,
Jimmy Tao
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |