topic Total not visible in Table in DAX Commands and Tips

Total not visible in Table

nikkr38 — Tue, 30 Jul 2024 19:42:38 GMT

Hi everyone,

I have a issue, where values are shown on row level but not at the total level.

Logic for the calculation has to be as followed:
1. We have to check Billings for the previous month when the Net Billings was not 0, then I have to sum Cost after that period when Net Billings is not 0.
2. If there no record where the Net Billings is not 0 then sum all the Cost.

Cost =

CALCULATE( SUM('Project Actuals'[Internal_Costs]), USERELATIONSHIP('Project Actuals'[Date],'Rev Rec'[Date])) <li-code lang="markup">Billings = SUM('Rev Rec'[Net Billings])

Below is the DAX which I have tried, but it's not giving me total.

Final Cost = VAR _currentmonth = SELECTEDVALUE('Rev Rec'[Date]) VAR _lastNonZeroDate = CALCULATE( LASTDATE('Rev Rec'[Date]), FILTER( ALL('Rev Rec'[Date]), 'Rev Rec'[Date] < _currentmonth && [Net Billings] <> 0 ) ) VAR _earliestDate = CALCULATE( MIN('Rev Rec'[Date]), FILTER( ALL('Rev Rec'[Date]), [Net Billings] = 0 ) ) RETURN IF( ISBLANK(_lastNonZeroDate), CALCULATE( [Internal Cost], 'Rev Rec'[Date] >= _earliestDate && 'Rev Rec'[Date] < _currentmonth), IF( _lastNonZeroDate <> BLANK(), CALCULATE( [Internal Cost], FILTER(ALL('Rev Rec'[Date]), 'Rev Rec'[Date] > _lastNonZeroDate && 'Rev Rec'[Date] < _currentmonth))Below image shows, the first scenario when we have a Value in Net Billings, so in this case I need sum after that period.

Second Scenario, when there is no values for previous months in Net Billings, then we have to sum everything in Cost.

I would appreciate your help, and if there are any better solution for this.

Thanks,
Nik

Re: Total not visible in Table

Anonymous — Wed, 31 Jul 2024 02:42:22 GMT

Hi nikkr38 ,

Here are the steps you can follow：

1. Create measure.

Measure = var _maxdate= MAXX( ALLSELECTED('Table'),'Table'[Date]) var _value= SUMX( FILTER(ALLSELECTED('Table'),'Table'[Date]=_maxdate),[Net Billings]) RETURN IF( _value<>0&&HASONEFILTER('Table'[Date]),SUM('Table'[Internal Cost]), SUMX( FILTER( ALLSELECTED('Table'),[Net Billings]<>0),[Internal Cost]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Total not visible in Table

nikkr38 — Wed, 31 Jul 2024 22:25:43 GMT

Hey Anonymous

I appreciate your efforts in solving this problem, but the soltuion is not correct.
I need only the sum of cost, after the most recent date when there was a value in Billings & if there is no value in billings for previous month then I need sum of all the internal cost.

FYI : Date has to be most recent when there was a value in Billing for previous months.

Let me know if you need, more info.

Thanks,
Nik