cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Elektryon Frequent Visitor
Frequent Visitor

Box and Whisker by MAQ Software (Measure as Category)

Hi Community

 

I have the following plot designed with Box and Whisker Visual by MAQ Software @MAQSupport:

BoxAndWhisker.PNG

 

 

The image shows the representation I would like to achieve. The ComparisonGroup Category contains 2 Values: "Focus" and "Peers":

BoxAndWhiskerData.PNG

 

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:

BoxAndWhisker Filter Companies.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Elektryon Frequent Visitor
Frequent Visitor

Re: Box and Whisker by MAQ Software (Measure as Category)

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:PeerCompanies.PNG

 

 

 

 

 

 

 

 

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.

Relationships.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:

 

Fields.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

which leads to the following representation:BoxAndWhisker_New.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To better understand what's happening in the background I have split up the values used in a matrix visual:Matrix.PNG

 

 

 

 

 

 

 

If we show additionally the columns with no data ("Show items with no data" context action) it becomes more clear what's happening:

 

MatrixWithEmptyColumns1.PNG

 

 

 

 

 

 

 

MatrixWithEmptyColumns2.PNG

 

 

 

 

 

We are now able to select different focus / peer companies and the visual adapts immediately Smiley Happy

 

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Box and Whisker by MAQ Software (Measure as Category)

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

 

Elektryon Frequent Visitor
Frequent Visitor

Re: Box and Whisker by MAQ Software (Measure as Category)

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

MAQSupport Member
Member

Re: Box and Whisker by MAQ Software (Measure as Category)

Hello @Elektryon,

 

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

 

Thanks.

Highlighted
Community Support Team
Community Support Team

Re: Box and Whisker by MAQ Software (Measure as Category)

Hi Elektryon,

 

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

 

Regards,

Jimmy Tao

Elektryon Frequent Visitor
Frequent Visitor

Re: Box and Whisker by MAQ Software (Measure as Category)

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:PeerCompanies.PNG

 

 

 

 

 

 

 

 

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.

Relationships.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:

 

Fields.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

which leads to the following representation:BoxAndWhisker_New.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To better understand what's happening in the background I have split up the values used in a matrix visual:Matrix.PNG

 

 

 

 

 

 

 

If we show additionally the columns with no data ("Show items with no data" context action) it becomes more clear what's happening:

 

MatrixWithEmptyColumns1.PNG

 

 

 

 

 

 

 

MatrixWithEmptyColumns2.PNG

 

 

 

 

 

We are now able to select different focus / peer companies and the visual adapts immediately Smiley Happy

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors