% of Contribution

Adrien
Adrien Member
in Charting

Hi,

I try to show the % of contribution and need some help with the beast mode.

Right now, I have a Pivot table. Store are grouped by Sales Leader and the timeline is "This Year, By Month".

I want to show the % of contribution of the store, in the Sale leader group and see evolution over month.

I have try this beast mode :

SUM(`TRANS_COUNT`) / SUM(SUM(`TRANS_COUNT`)) over ()

It work, but it gave me the % for the month over the total of the year.

If I add partition by month(date) it dosent work.

If I modify it like this :

SUM(`TRANS_COUNT`) / SUM(SUM(CASE 

when MONTH(`DATES`) = 12 then TRANS_COUNT

when MONTH(`DATES`) = 11 then `TRANS_COUNT`

when MONTH(`DATES`) = 10 then `TRANS_COUNT`

when MONTH(`DATES`) = 9 then `TRANS_COUNT`     

when MONTH(`DATES`) = 8 then `TRANS_COUNT`

when MONTH(`DATES`) = 7 then `TRANS_COUNT`

when MONTH(`DATES`) = 6 then `TRANS_COUNT`     

when MONTH(`DATES`) = 5 then `TRANS_COUNT`

when MONTH(`DATES`) = 4 then `TRANS_COUNT`

when MONTH(`DATES`) = 3 then `TRANS_COUNT`     

when MONTH(`DATES`) = 2 then `TRANS_COUNT`     

when MONTH(`DATES`) = 1 then `TRANS_COUNT`     else 0 end)) over (partition by MONTH(`DATES`), YEAR(`DATES`))

That one work. But I dont have subtotal anymore... what can I do to have subtotal back in the pivot table ?

Comments

  • Adrien
    Adrien Member

    OK sorry so I have made an error, but still have my problem.

    With the last formula in my first post, I see everything but the % is against the month. So that's good.

    But I try to have it against the total for the Sale leader group.

    I have try to add 'Sale Leader' in the partition by, but that's when my subtotal disapear ! (but I have the % against the total of the sale leader..)

Categories