Summing measure values from table

[Anonymous]

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

[v-danhe-msft]

Hi @Anonymous,

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

Regards,

Daniel He

[Anonymous]

Hi

I am sorry for the delay, but i prepared raw data with this function for you to look up.

https://nestle-my.sharepoint.com/:u:/r/personal/michal_radkowski_cppl_nestle_com/Documents/Bad%20Goo...

If you know how to do it faster/better or how to sum it up please let me know.

Thank you in advance

Radkos

[v-danhe-msft]

Hi @Anonymous,

It seems that I have no licnese to access your file:

Could you please upload your report to your OneDrive or Dropbox and send the link to me ?

Regards,

Daniel He

[Anonymous]

Hello again, 

https://www.dropbox.com/s/ik8sr8id8lkcspl/Bad%20Goods%20TEST.pbix?dl=0

I have a problem with sending this by dropbox, maybe this is a good link

Best Regards

[affan]

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]

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]

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