Box and Whisker by MAQ Software (Measure as Category)

[Anonymous]

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:

1. Change the parameter selection

   'Focus Company'[Focus Company]

   to a different company
2.  Accept the change in the model ("There are pending changes in your queries that haven't been applied."
3.  Reload the whole dataset.

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

[Anonymous · Accepted 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]
    )

• VAR TotalRevenueGrowthRateFocusCompany calculates the KPI value we are interested in for a selected focus company
• VAR TotalRevenueGrowthRatePeerCompanies calcutes the KPI value for companies selected in "Company peers selection"
• In the return part I make sure that CompanyWithPeer[Company] field is filtered/selected on (HASONEVALUE). If we do have one value in the current filter context we decide upon the selected value to return TotalRevenueGrowthRateFocusCompany for the focus company, or to return TotalRevenueGrowthRatePeerCompanies to the "Peers" row (the one we added in the CompanyWithPeer table), otherwise we return BLANK().

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

[MAQSupport]

Hello @Anonymous,

Due to the design limitations of Box and Whisker by MAQ Software, we cannot add support for measures in Axis category.

Thanks.

[v-yuta-msft]

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

[Anonymous]

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

[v-yuta-msft]

Hi Elektryon,

In this senario, I'm afraid you should consider to use other visuals instead.

Regards,

Jimmy Tao