Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Summing measure values from table

Hello,

I have got a problem. I wrote a measure that takes 3 different tbles into account and have over 5 calculate functions with different filters, allexcepts, distinct countrows,sums and so on in it.

If I will have a table that makes my measure work, is there a possibility to sum all of the cells from the table in other measure?

Below a screen of my table

 

 

 

Thank you in advance

Need Help

7 Replies

  • affan's avatar
    affan
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Hi Anonymous,

     

    If you are using table visual you can get the total of the column by default. If you are having trouble getting the total please share the pbix file 

     

    Regards,

    Affan

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello affan

       

      This case in not that simple, in the column we have a measure, that takes filtered rows from 3 different datasets, when i show total I have 0, because I cant put that measure on all table. here is my measure, so you could see, why i cant just sum it up.

       

      Bad Goods Projected = IF(
      CALCULATE(SUM('Merge1'[FreshNes of Inventory.Value]),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant],
      'Merge1'[FreshNes of Inventory.Attribute2]),FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]>=[Min Aging Value]),
      FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]<[Max Aging Value]))/CALCULATE(COUNTROWS('Merge1'),
      ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant],'Merge1'[FreshNes of Inventory.Attribute2]),
      FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]>=[Min Aging Value]),
      FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]<[Max Aging Value]))
      *CALCULATE(DISTINCTCOUNT('Merge1'[FreshNes of Inventory.Attribute2]),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant]),
      FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]>=[Min Aging Value]),
      FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]<[Max Aging Value]))
      - CALCULATE(SUM('Merge1'[Stock Location - Demand.Value]),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant]),
      FILTER('Merge1','Merge1'[Stock Location - Demand.Week]<=[ActWeek]+[LAG]))/3<0
      ,0,
      CALCULATE(SUM('Merge1'[FreshNes of Inventory.Value]),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant],
      'Merge1'[FreshNes of Inventory.Attribute2]),FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]>=[Min Aging Value]),
      FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]<[Max Aging Value]))/CALCULATE(COUNTROWS('Merge1'),
      ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant],'Merge1'[FreshNes of Inventory.Attribute2]),
      FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]>=[Min Aging Value]),
      FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]<[Max Aging Value]))
      *CALCULATE(DISTINCTCOUNT('Merge1'[FreshNes of Inventory.Attribute2]),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant]),
      FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]>=[Min Aging Value]),
      FILTER('Merge1','Merge1'[FreshNes of Inventory.Attribute2]<[Max Aging Value]))
      - CALCULATE(SUM('Merge1'[Stock Location - Demand.Value]),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant]),
      FILTER('Merge1','Merge1'[Stock Location - Demand.Week]<=[ActWeek]+[LAG]))/3)

      *CALCULATE(SUM('Merge1'[Price]), ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code]),'Merge1'[Plant])
      /CALCULATE(COUNTROWS('Merge1'),ALLEXCEPT('Merge1','Merge1'[Material],'Merge1'[Company code],'Merge1'[Plant]))

       

       

      Best Regards

      Radkos

      • affan's avatar
        affan
        Icon for Solution Sage rankSolution Sage
        7 years ago

        Hi

         

        You are right that it is not that simple to add the total, however if you can share the file I can give it a try.

         

        Regards.

        Affan

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

    Could you please share the pbix file to have a test?

     

    Regards,

    Daniel He