Percent of total YTD (urgent, please help)

[Adelaida]

Hi to all,

I need to put in a report the YTD sales and % of total of dimensions for YTD.
I know that it is not possible to create aggregation of an aggregated measure so I made YTD with the formula below:

IF ( ( 'SALES_DATE'n <= DateFromMDY( Month(DatePart(Now())), DayOfMonth(DatePart(Now())), Year('SALES_DATE'n))) )
RETURN 'FT_GROSS_AMOUNT'n
ELSE 0

If I put the formula below in an pivot without dimensions the overall sum is OK.
If I put the Sum [_ForAll_] ('Ytd Gross Act'n) for the current year is OK, for all the other is much more than the total of YTD for the year. And if I create % of total for Ytd column it sums to 38% instead of 100%.
Is that a bug?
How can I fix it?

Please help!
Thanks in advance,

Adelaida

PS I discover what Sum [_ForAll_] ('Ytd Gross Act'n) gives as result , it gives the same as Sum [_ForAll_] ('FT_GROSS_AMOUNT'n). But why? It works fine if I make a condition on product dimension or other. Is it something wrong in my formula below?

[hargan · Accepted solution]

Hello,

In simpler words,

If you wants to find %of any column, then you can write click on column name (measure) and then click on "Create" and then click on "Percentage of Total".

Let's say column name is PROFIT.

so, you can make a new aggregated column as a Percenatge Total of PROFIT.

Thanks

[Adelaida]

Hai Hargan,

It works for calculated items, but not for aggregate measures. If you create Year to date column for Profit, you cannot create % of Year to date. That's why my formula.
And yes, I have made % of the column calculate as
IF ( ( 'SALES_DATE'n <= DateFromMDY( Month(DatePart(Now())), DayOfMonth(DatePart(Now())), Year('SALES_DATE'n))) )
RETURN 'FT_GROSS_AMOUNT'n
ELSE 0
But as you can see from the image attached the result is strange (the total line is 38% instead of 100%). The problem is that VA consider the total equal to Sum [_ForAll_] ('FT_GROSS_AMOUNT'n) instead of Sum [_ForAll_] (IF ( ( 'SALES_DATE'n <= DateFromMDY( Month(DatePart(Now())), DayOfMonth(DatePart(Now())), Year('SALES_DATE'n))) )
RETURN 'FT_GROSS_AMOUNT'n
ELSE 0) so % form total is strange, is buged...
Any idea?
Thanks,

Adelaida

[hargan]

Hello,

Can you please share your dataset and objective of your task if it's not confidential ?
In any case you can mail me i.e (hargan.gandhi@gmail.com)

Thanks

[Adelaida]

Hi Hargan, I have solved it temporary using your hit. I hope they won't need a graphic of the YTD % of total, because there I must use Sum [_ByGroup_] Ytd / Sum [_ForAll_] Ytd and it's bugged. Till then I consider the problem solved. Thank you, Adelaida

[hargan]

Sure.

If you need let me know, will find any solution for the same.

Thanks

[Frank_Boekamp]

Just some thoughts here: What goes wrong when using the supplied functionality for calculating YTD aggregated measure? You can do some calculations on aggregated measures like

Sum [_ByGroup_] aggregated measure / Sum [_ForAll_] aggregated measure