1 2 Previous Next 19 Replies Latest reply on Jun 14, 2019 9:10 AM by Hari Ankem

# To get grand total of a measure without splitting it in ship mode like other measures in the grid...

Hi Guys,

I am facing an issue in which I have to show grand total of a measure without letting it split into any dimension, but rest measure should be split, as shown below in an example:

Suppose I have two measure like Sales and Profit in a singel report and ship mode to show the split of these measure, but i just want to split Sales and not profit by Ship Mode.

I want something like this in the end, Sales with ship mode slipt and profit with totals but without any ship mode split.

I tried showing Profit as discrete, but for discretes we don't get the Grand Totals.

Does anyone have any easy solution for the same? without altering anything in the datasource can this be achieved?

Regards,

Jayshree Dawrewar

• ###### 1. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

Hi Jayashree,

Find the attached workbook

Final O/P:

Hope this helps

BR,

NB

• ###### 2. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

Will this work?

3 of 3 people found this helpful
• ###### 3. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

Hi Hari sir,

I have tried the count distinct approach but grand totals are not working properly is there any way we can get the grand totals correct

i have provided the solution using table calculation

IF FIRST()=LAST()

THEN

SUM([Profit])

ELSE

SUM([Sales])

END

because its table calculations it comes with limitations.

Thanks for teaching the alternate new approach sir

BR,

NB

• ###### 4. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

Hi Naveen B and Hari,

Thank you for such prompt response, I did this as well but the issue is in real case, i have more than two measures and not just profit or sales...

I can't share the real scenario so i just created one with superstore data. Can this be expanded for more than two measures?

Regards,

Jayshree

• ###### 5. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

Hi Jayashree,

Then you need to create 2 sheets and place side by side  in dashboard

BR,

NB

• ###### 6. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

Like attached

BR,

NB

• ###### 7. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

Naveen: I am not seeing any issue in this case. Can you show me the scenarios where it's not working?

• ###### 8. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

Can you provide a drawing of your expected output with 2 or more measures? I would suggest providing dummy data with your real case so that we can see whether it's really possible or not. Otherwise, there is always a solution where you can merge multiple sheets on a dashboard as suggested by Naveen.

• ###### 9. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

hi sir,

now i am able to get it i changed the total using automatic instead of sum

thanks for the approach

BR,

NB

• ###### 10. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

You are welcome Naveen.

• ###### 11. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

Hi Hari,

A question tho. I downloaded your file and try to replicate your sheet 1 on another sheet 3, It shows your ship mode includes Profit where mine(sheet 3) does not include profit. Can you explain how this is done? Thank you.

• ###### 12. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

You need to enable the grand totals for the rows (and columns too, if needed). Click on "Analysis - Totals - Show Row Grand Totals". And once the grand totals are displayed, format the grand totals to change the label to show what you need.

1 of 1 people found this helpful
• ###### 13. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

Neat. Something new I learnt today. Thanks!

• ###### 14. Re: To get grand total of a measure without splitting it in ship mode like other measures in the grid...

I want something like this without creating multiple sheets. As this is the need of business.

Regards,

Jayshree

1 2 Previous Next