cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

 

01102018 screen table sum problem2.png

 

 

Thank you in advance

7 REPLIES 7
affan Established Member
Established Member

Re: Summing measure values from table

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
Not applicable

Re: Summing measure values from table

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 Established Member
Established Member

Re: Summing measure values from table

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

Microsoft v-danhe-msft
Microsoft

Re: Summing measure values from table

Hi @Anonymous,

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Summing measure values from table

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

Microsoft v-danhe-msft
Microsoft

Re: Summing measure values from table

Hi @Anonymous,

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

1.PNG

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

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Summing measure values from table

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors