Set Analysis little issue

[alec1982]

Hi Guys,

I am trying to use the following formula within a bar chart to get an average

=Sum({1<[Type]={'A'}>}[Amount])/Sum({1}B)

The Dimension is C.

I need to get a straight line of average across the bar chart. Not sure what I am doing wrong here.. I've done this hundred times before with no issue.

The formula above returns a zigzag line that moves with the bars..

Let me know your thoughts and thanks in advance.

Best,

Alec

[MK_QSL]

Dimension = C

=Sum({1<[Type]={'A'}>}[Amount])/Sum({1}B)

Let me clarify...

B = amount

Amount = Amount (Is B and Amount is different?, sorry for my stupid question, my intention is to help you..)

C = State

Type = any Type (i.e. Dimension)

If you give us three four lines data for above dimensions and your end results, would better ...!

[Brice-SACCUCCI]

What about

=Sum({<[Type]={'A'}>}[Amount])/ Sum ({1}B)

?

[alec1982]

Sure, thank you for your help

C          Type     Amount     Count

GA        A         200          25          

GA        B         250          25

CL         A        150          20

NC         B        350          35

NY         B        175          45

=Sum({1<[Type]={'B'}>}[Amount])/Sum({1}Count)

[alec1982]

this returns different value for each bar

[MK_QSL]

I am confused now.

You said B is an amount and here B is a Type..!!

[MK_QSL]

Try This...

=SUM({1<Type = {'A'}>}Amount)/COUNT({1}Count)

[alec1982]

Sorry, I changed the structure and added the formula below it o it will be clear.

[MK_QSL]

Is my above answer working?

if not, let me know your logic of average. I will come back to you on this.

[alec1982]

This logic doesn't work. it gives a zigzag line which means a different value for each bar of the dimension.

[MK_QSL]

Create a variable

VAR  =SUM({1<Type = {'A'}>}Amount)/COUNT({1}Count)

= should be there in variable definition

Now Chart expression should be

$(var)