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

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

    Ahmed Mukhtar

      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

       

      ProductChannelPublisher
      FurnitureSocialFacebook
      FurnitureSocialTwitter
      FurnitureSocialInstagram

       

      2. Datasource1 (Facebook)

       

      ProductChannelPublisherBudget
      FurnitureSocialFacebook10000

       

       

      3. Datasource2 (Instagram)

       

      ProductChannelPublisherBudget
      FurnitureSocialInstagram20000

       

      Note that for Twitter, I dont have any datasource

       

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

       

      Budget:

       

       

      1. if ATTR([Publisher])="Facebook" 
      2. then SUM([Facebook].[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

       

      ProductChannelPublisherTotal Budget
      FurnitureSocialFacebook10000
      FurnitureSocialTwitter
      FurnitureSocialInstagram20000
      Total30000

       

      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

       

      Will really appreciate your help