YTD Pie Chart

[bobbydave]

sum({<Grouping={'BUSINESS*', 'DATA CENTRE', '*INFR*', 'IT OTHER', 'IT SERVICES', 'PROJECTS'},
Breakdown={'Total YTD'}, Year={'2014'}>} [Metric Value])/sum({<Year={'2014'}>}[Metric Value])

I'm creating a pie chart that is divided into 5 groups. I want to sum their totals (YTD) and divide them by the total 2014 YTD values .

The above formula seems to be giving me between 33-36% for each grouping which is incorrect. Should be between 2% and 50% for each grouping.

Anyone spot something I've done wrong?

[krishna20]

Hi Dave,

Why are you using single quotes at 2014?

Regards

Krishna

[bobbydave]

Noted!

I've changed them to double quotes.

Don't think this would impact the end result.

[krishna20]

Hi,

Create a variable for your field as vCurrentYear=Max(year).

Apply your expression like this

sum({<Year={$(vCurrentYear)},Grouping={'BUSINESS*', 'DATA CENTRE', '*INFR*', 'IT OTHER', 'IT SERVICES', 'PROJECTS'},
Breakdown={'Total YTD'}>} [Metric Value])/sum({<Year={$(vCurrentYear)}>}[Metric Value])

Regards

Krishna

[bobbydave]

I get 'no data to display'.

The formula does look correct right?

I want to get a percentage of the total YTD values of each group and divide that by the total overall YTD value.

Would my formula above formulate such a result?

[bobbydave]

If I create text boxes and put individual values in per group, the correct values are going in.

However if I put them in as a whole, they all come out as 100% each.

[#7]

According to this:

bobbydave wrote:

I'm creating a pie chart that is divided into 5 groups. I want to sum their totals (YTD) and divide them by the total 2014 YTD values .

You're probably missing the Breakdown={'Total YTD'}  in the divider:

[...] /sum({<Breakdown={'Total YTD'}, Year={$(vCurrentYear)}>}[Metric Value])

[bobbydave · Accepted solution]

I just needed to make individual Grouping expressions.

I was trying to create 1 expression with the groupings in this expression.

Instead I created individual expressions and issue resolved.