6 Replies Latest reply on Nov 10, 2018 11:48 PM by Ahmed Mukhtar

Problem with Grandtotal and sum with blended calculated field ~ Help~~

Hi,

Can someone help me in the following

I am using Data blending and when sum the fields, it doesnt display grandtotal and doesnt display the sum of the two blended fields

I am explaining the scenario in detail with a sample data

Datasources:

1.PrimaryDatasource

 Product Channel Publisher Furniture Social Facebook Furniture Social Twitter Furniture Social Instagram

 Product Channel Publisher Budget Furniture Social Facebook 10000

3. Datasource2 (Instagram)

 Product Channel Publisher Budget Furniture Social Instagram 20000

Note that for Twitter, I dont have any datasource

I am now using a calculated field in the primary datasources as follows

Budget:

3.
4.
5. elseif ATTR([Publisher])="Instagram"
6. then SUM([Instagram].[Budget])
7.
8.
9. END

Desired Output 1:

I want to use primary datasource as a main datasource and blend the other two datasources with product, channel, publisher and have a total budget with a grandtotal

 Product Channel Publisher Total Budget Furniture Social Facebook 10000 Furniture Social Twitter Furniture Social Instagram 20000 Total 30000

Problem 1:

1) Grandtotal: Because the Twitter is not there, I am unable have a grandtotal of the displayed. Its NULL

Desired Output 2:

I want to use a primary datasource publishers to select multiple publishers as a filter in a dashboard which displays the sum of budgets (based on selection)

e.g.

a) If I click Facebook it should display 10000.

b) If I click facebook and Twitter both, it should still display 10,000

c) if I click all three, it should display 20,000

Problem 2:

Case a works but Because of the blending, when I select more than 1 datasource, it display null for case b and c

I have attached the workbook

Sheet 1 is problem 1 and dashboard is problem 2

• 1. Re: Problem with Grandtotal and sum with blended calculated field ~ Help~~

I haven't had a chance to look at the workbook, but try wrapping the SUM functions in zn() to zero the NULLS out.  It should add properly.  Right now you are adding a number to a NULL, which will result in a NULL.

3. elseif ATTR([Publisher])="Instagram"
4. then zn(SUM([Instagram].[Budget]) )
5. END
• 2. Re: Problem with Grandtotal and sum with blended calculated field ~ Help~~

Hi Micheal,

The nulls are actually from the missing Twitter field I believe.

I tried zn with the non-zero ones but didnt give the desired output

• 3. Re: Problem with Grandtotal and sum with blended calculated field ~ Help~~

Would you be able to look into the workbook please.....

• 4. Re: Problem with Grandtotal and sum with blended calculated field ~ Help~~

See below KB Article.  This is a known issue and there are solutions proposed within:

• 5. Re: Problem with Grandtotal and sum with blended calculated field ~ Help~~

This is what I tried

if FIRST()=0 and ATTR([Publisher])="Facebook" THEN WINDOW_SUM(

elseif FIRST()=0 and ATTR([Publisher])="Instagram" THEN WINDOW_SUM(

zn(SUM([Instagram].[Budget])))

END

The results are same Can you, kindly, try it with the sample workbook?

Thanks alot!

• 6. Re: Problem with Grandtotal and sum with blended calculated field ~ Help~~

Anyone can help?