Forum Discussion
Missing totals
Hi, I have been having issue of not getting totals in my calculations when using Table visualization or otherwise.
Formulas I am using:
1. _Loadability by cases per trips =
CALCULATE(
DIVIDE([0_Total Volume Shipped by Cases],[0_Total Shipments],BLANK()
))
2. last month loadability BCPT =
CALCULATE(
[ _Loadability by cases per trips ],DATEADD (dDate[Date],-1,MONTH)
)
3. Trips saved VS last Month =
CALCULATE(
IF(
[last month loadability BCPT]=0,BLANK(),CALCULATE(
(DIVIDE ( [0_Total Volume Shipped by Cases] , [last month loadability BCPT] , BLANK()
) - [0_Total Shipments])
)))
One approach is to keep your current measures and make new ones with this pattern (assuming you have a YearMonth column in your Date table):
NewMeasure = sumx(Values(Date[YearMonth]), [existing measure])
That should work both in your rows for each YearMonth and for the total.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
2 Replies
- mahoneypat
Microsoft Employee
One approach is to keep your current measures and make new ones with this pattern (assuming you have a YearMonth column in your Date table):
NewMeasure = sumx(Values(Date[YearMonth]), [existing measure])
That should work both in your rows for each YearMonth and for the total.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Year measures are written... well, suboptimally, to say it mildly. Here are the correct versions:
1. [_Loadability by cases per trips] = DIVIDE( [0_Total Volume Shipped by Cases], [0_Total Shipments] ) 2. [last month loadability BCPT] = CALCULATE( [_Loadability by cases per trips], DATEADD (dDate[Date], -1, MONTH) ) 3. [Trips saved VS last Month] = var __loadability = [last month loadability BCPT] return if( __loadability, DIVIDE ( [0_Total Volume Shipped by Cases], __loadability ) - [0_Total Shipments] )As for the totals... You have not given the rule for their calculation, so nobody can tell you what they should be.
Best
D