Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @Anonymous,
Could you please share the pbix file to have a test?
Regards,
Daniel He
Hi
I am sorry for the delay, but i prepared raw data with this function for you to look up.
If you know how to do it faster/better or how to sum it up please let me know.
Thank you in advance
Radkos
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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |