Measures resulting in blank total, solution best practices

[Mark1982]

Hi,

Me again with a question. 

I am working on a very complex power bi report doing a lot of fiscal year calculations in covering current and previous W,M,Q,Y.

Now i like to think my fiscal year, quarter solution is pretty solid. The measure syntax is pretty straightforward. But iam hitting a bit of anu issues with the totals. The standard SUM leaves the totals empty, which is not desired. Now i've done a fair amount of digging whereby the solution is to using the SUMX within the measure to solve this issue.

In general i am seeking some advice what the best approaches are. I am just not knowledgable enough on the use of SUMX, SUMMARIZE and related functions.

Here we go:

I've got this measure of calculating the Past / Prior Quarter.

Sales Amount PQ =

VAR __MeasureDate = LASTDATE('Date'[Date]) // Fiscal quarter selected in slicer

VAR __MeasuredFiscalQr = CALCULATE(MIN('Date'[FiscalQuarterKey]),FILTER('Date','Date'[Date] = __MeasureDate)) // Get FiscalQuarterKey beloging to measured date

VAR __PastFiscalQr = __MeasuredFiscalQr - 1 // FiscalQuarterKey - 1 is past quarter

VAR __Amount = CALCULATE(SUM(GeneralSales[Amount]), USERELATIONSHIP(GeneralSales[Date],'Date'[Date]), FILTER( ALL ('Date'),'Date'[FiscalQuarterKey] = __PastFiscalQr))

RETURN ( __Amount)

Using this will result in the following. Check the blank total.

Now from various sources i've learned that SUMX would solve my issue (and it does).

Sales Amount PQ SUMX =

VAR __Amount = SUMX(VALUES('Date'[FiscalQuarterKey]),[Sales Amount PQ])

RETURN ( __Amount)

But using this pattern would mean all my fiscal measures require an additional SUMX measure. Is that really the best approach? I would like to see how both can be combined.

Is there a better way of approaching this? What is the best practise pattern covering these kind of measures?

Please find below a link to the power bi file.

https://1drv.ms/u/s!Ag3hOdYVKV71qkVw9go35YozMPbW?e=BYGc5n

[Mark1982]

The user would like to be able to view the current period vs the prior. This includes the total. Further in my model (not in the example provided) there are some variance and difference calculations. The solution provided will not result in correct calculations. 

From your response i am getting that using an additional measure SUMX as wrapper is good practise. I am understanding that correctly?

[AllisonKennedy]

@Mark1982  Yes, SUMX is good practice and better performance, so get into that habit and you'll save yourself a lot of headaches.

Does that mean you have resolved the issue with use of SUMX? If you're still looking for a solution please provide more detail on the variance, etc you omitted from your example so we can provide an accurate solution that is comprehensive, otherwise mark one of these posts as a solution or write up a summary and mark that summary as the solution so others can learn too. 

SUMX is awesome in general. Probably more info than you're looking for, but SQLBI are my go to source for DAX learnings: https://www.sqlbi.com/blog/alberto/2011/10/26/sum-or-sumx-with-simple-intermediate-calculations-sumx... 

[AllisonKennedy · Accepted solution]

@Mark1982 Why do you want the total to not be blank? It's a bit confusing to the end user that you've named it 'Sales Amount PQ' but the total actually provides the total Sales Amount for all time (this is due to the date context not being provided in the total row). 

SUMX is a good measure to use and get in the habit of using. 

If you will always provide a quarter context, or want to use the quarter associated witht the lastdate in context (since you used LASTDATE function in your measure) you could also update your measure to remove the FILTER expression: 

Sales Amount PQ =

VAR __MeasureDate = LASTDATE('Date'[Date]) // Fiscal quarter selected in slicer

VAR __MeasuredFiscalQr = CALCULATE(MIN('Date'[FiscalQuarterKey]),FILTER('Date','Date'[Date] = __MeasureDate)) // Get FiscalQuarterKey beloging to measured date

VAR __PastFiscalQr = __MeasuredFiscalQr - 1 // FiscalQuarterKey - 1 is past quarter

VAR __Amount = CALCULATE(SUM(GeneralSales[Amount]), USERELATIONSHIP(GeneralSales[Date],'Date'[Date]), 'Date'[FiscalQuarterKey] = __PastFiscalQr)

RETURN ( __Amount)

And that should populate the total row with the last value. In the screenshot above it will still be blank, but if you add a date slicer and select a date in this quarter, (2021/Q3) it will show 350 in the total row.