11 Replies Latest reply: Feb 20, 2018 6:01 AM by Kizzy Davies

# How can I have a bar chart that displays 1 measure but is limited by another

Hello,

I need to create the following;

Dimension (displayed) - country

Measure (displayed) - number of days

Measure (hidden) - number of sales

The number of sales measure should not be shown but the chart needs to only pick up the top 4 countries by number of sales and display the number of days for those countries.  Right now I can only select the top 4 by number of days.

Thanks,

Kizzy

• ###### Re: How can I have a bar chart that displays 1 measure but is limited by another

Hi try this

Take a Barchart take a Dimension-Country

Expression -sum(Days)

In sorting for Country select ''Sort by Expression '' and write sum(sales) in expression and select Ascending order

• ###### Re: How can I have a bar chart that displays 1 measure but is limited by another

Hi Thirumal,

Thanks for the response but it is still only showing Days.

Please see below the actual expressions (I used days and Sales as a simple example).

The Dimension - Location/Region has been limited to only show the top 4 but this is picking up Measure 1 and I need it to pick up Measure 2.  I have inlcuded a screen shot of the sort as per your instructions.

Please can you see what I've done wrong.

Thanks,

Kizzy

• ###### Re: How can I have a bar chart that displays 1 measure but is limited by another

You don't need to add another measure, just add the condition to show top 4 in the set analysis of the expression for number of days

For example

Dimension (displayed) - country

Measure (displayed) - number of days

=Count({<Country  = {"=RANK(Sum(Sales))<=4"}>}Days)

• ###### Re: How can I have a bar chart that displays 1 measure but is limited by another

Hi Vineeth,

The current expressions are

Measure 1 -
Sum({\$<[Requisition Status]={Closed},[Division]={'Lifestyle Audio'}>}[Approve to Close])/Sum({\$<[Requisition Status]={Closed},[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs])

Measure 2 -

Sum({\$<[Requisition Status]={Closed},[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs])

Please advise how I would recreate with the actual expressions.  Also I have several other bar charts that need the same type of filter in which the first measure is different, do i need to create an expression for each chart to get the top 4?

Thanks,

Kizzy

• ###### Re: How can I have a bar chart that displays 1 measure but is limited by another

few questions:

what is measure 1 for ?

what is measure 2 for ?

which one of these do you want to show in the chart?

how do you want to limit the dimensions by ? top 4 by measure 1 or top  by measure 2 ?

• ###### Re: How can I have a bar chart that displays 1 measure but is limited by another

Hi Vineeth,

Measure 1 - shows the Time to Fill calculation (including filters) - Sum of 1 field divided by the sun of another field - This should be the figure that is displayed in the bar chart

Measure 2 - shows the total number of hires (including filters)

I want the bar chart to pick the top 4 total number of hires (measure 2) but to display the Time to Fill (measure 1) in the actual chart.  The second measure should not show anywhere on the actual chart it is for filtering only.

Hope this makes sense.

Thanks,

Kizzy

• ###### Re: How can I have a bar chart that displays 1 measure but is limited by another

ok,try below and remove Measure 2

=if( RANK( Sum({\$<[Requisition Status]={Closed},[Division]={'Lifestyle Audio'}>}[Number of Filled Reqs]) )<=4,

Sum({\$<[Requisition Status]={Closed},[Division]={'Lifestyle Audio'}>}[Approve to Close])

/Sum({\$<[Requisition Status]={Closed},[Division]={'Lifestyle Audio'}>}

[Number of Filled Reqs])

)

• ###### Re: How can I have a bar chart that displays 1 measure but is limited by another

Hi Vineeth,

This works - thanks so much for your help!

Kizzy

• ###### Re: How can I have a bar chart that displays 1 measure but is limited by another

replace existing dimension with

=AGGR(

if( RANK ( SUM (Sales) )<= 4 , Country)

, Country )

and then check suppress null values

• ###### Re: How can I have a bar chart that displays 1 measure but is limited by another

Hi Vineeth,

The measure works but shows all the countries which are not in the top 5 (see screen shot).  I don't want to see these.  I tried using the AGGR dimension you provided as this looks like it will fix the issue but couldn't get the it to work as it was only delivering 1 column.  Please could you help.

Thanks,

Kizzy