cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Showing Multiple Measure in one bar chart cleanly

Hi PowerBIers,

 

I have what I think should be a simple question but cant seem to find the answer to it anywhere.

 

I have market research data, where I have the awareness for a number of brands as different measures. 

 

Extract of Raw data like this

 IDAware Brand1Aware Brand2 Aware Brand3 Aware None 
1Brand1Brand2  
2Brand1   
3 Brand2  
4 Brand2  
5   None
6  Brand3 
7Brand1Brand2Brand3 
8 Brand2  
9 Brand2  
10Brand1Brand2  
11Brand1Brand2  

 

I make measures in Powerbi to derive the % aware for each brand

Aware Brand1 % = count(Data[Aware Brand1])/COUNT(Data[ID])*100

Aware Brand12% = count(Data[Aware Brand2])/COUNT(Data[ID])*100

 

When I want to chart this as a bar chart is is looks pretty bad in PowerBI. Eg I put each indivudal created measure into the value field with no Axis or legend data field. There is no way to get X axis labels, the bars are all squashed together. 

 

Is there a better way of doing this that I am missings so I can use more of the Charting power of PowerBI with data like this?

 

Thanks for the help.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: Showing Multiple Measure in one bar chart cleanly

Hi ,

 

Bit of a workaround but you could create a new table (by using Enter Data) with the name of your measures:

 

Table Name: Aware
Aware Headers:
Aware Brand1
Aware Brand2
Aware Brand3
Aware None

 

Then create a switch statement like the below:

 

SWITCH (
    MIN ( 'Aware'[Aware Headers] ),
    "Aware Brand1", [Aware Brand1],
    "Aware Brand2", [Aware Brand2],
    "Aware Brand3", [Aware Brand3],
    "Aware None", [Aware None]
)

 

Use the switch statement above as your value and use your Column Headers from the table as your X axis

View solution in original post

4 REPLIES 4
Highlighted
Resolver I
Resolver I

Re: Showing Multiple Measure in one bar chart cleanly

Hi ,

 

Bit of a workaround but you could create a new table (by using Enter Data) with the name of your measures:

 

Table Name: Aware
Aware Headers:
Aware Brand1
Aware Brand2
Aware Brand3
Aware None

 

Then create a switch statement like the below:

 

SWITCH (
    MIN ( 'Aware'[Aware Headers] ),
    "Aware Brand1", [Aware Brand1],
    "Aware Brand2", [Aware Brand2],
    "Aware Brand3", [Aware Brand3],
    "Aware None", [Aware None]
)

 

Use the switch statement above as your value and use your Column Headers from the table as your X axis

View solution in original post

Highlighted
Regular Visitor

Re: Showing Multiple Measure in one bar chart cleanly

Perfect thank you. It worked just as you described.

 

(It is a shame you have to use work arounds for this data setup. For market research it is quite a standard.)

 

Jon

Highlighted
New Member

Re: Showing Multiple Measure in one bar chart cleanly

Hi there, I have been having this same issue and wondering how the MIN('Aware'[Aware Headers]) statement works. Is this taking the minimum of all the fields? I attempted this, but it didn't work as the MIN() statement only allows 2 arguments. I have searched everywhere, and this solution seems to be one of the few that works, so please help as soon as you can!

Highlighted
Regular Visitor

Re: Showing Multiple Measure in one bar chart cleanly

As far as I am aware the Min fuction is ensuring only one value is passed through to the Switch function when it is searching through each of the values. Docs.Microsoft sums it up nicely. For switch you need to have - "Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context)."

 

In the mean time I have developed what I think is a better approach to this issue. (I am talking about research data here, so row level per respondent with these multi-choice questions)  I create a duplicate of my main data table. just with Respondent ID, and the multi choice questions. I then use unpivot to give me a flat table. Which looks like this

RespondentID        Value

1                            Aware Brand 1

1                           Aware Brand 2

2                           Aware Brand 1

3                          Aware brand 2

3                          Aware brand 3

 

I then link this back to the main table via respondent ID  and you can easily build charts from this. You can even add a third column to this being question number and then have all of your multiple choice questions within one table.

This is much more efficent from a data model persepctive as I found the Switch function was killing performance when I had alot of them in the tables with lots of brands.

 

Hope that helps.

 

 

 

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors