Grand total in measure for selected years

[sonuojha1]

Hello All,

I was trying to get Yearly GRAND TOTAL in to a measure,

CALCULATE([MEASURE],ALLEXCEPT('Dim - Date', 'Dim - Date'[Year]))

CALCULATE([MEASURE],ALLEXCEPT('Dim - Date', 'Dim - Date'[Month]))

CALCULATE([MEASURE],ALLEXCEPT('Dim - Date', 'Dim - Date'[Date]))

 But still I couldn't get what I am looking for.

I need yearly Grand Total in measure for example selected year = 2020, then grand total measure should show 548,125. Measure is giving sum of entire data for that column.

Regards,

Sonu

[Ashish_Mathur]

Hi,

Ensure that:

1. There is a relationhip from the Date column in your Data Table to the Date column in your Dim - Date Table
2. To your slicers and the visual, you have dragged Year and Month from the Dim - Date Table.
3. Write this measure

=calculate([measure],allexcept('dim - date','dim - date'[year]))

If this does not help, then share the link from where i can download your PBI file.

[sonuojha1]

Thanks for reply Ashish,

Yes offcourse, I have a join with my date dimension. May be you couldn't understand my query.

Whatever the grand total I am getting that is giving me entire data grand total. However I am looking for Yearly subtotal in a measure.

https://drive.google.com/file/d/1ihvtkKfZGGqZtCzKlEqMtJy2zZtSlvxP/view?usp=sharing 

[v-jayw-msft · Accepted solution]

Hi @sonuojha1 ,

Modify the measure as below.

Yrly Grand Total = CALCULATE(sum('Fact - Data'[VALUE1]),FILTER(ALL('Dim - Date'),'Dim - Date'[Date].[Year] in VALUES('Dim - Date'[Date].[Year])))

Result would be shown as below.

Best Regards,

Jay

[Ashish_Mathur]

Hi,

Download the PBI file from here.

Hope this helps.

[Greg_Deckler]

@sonuojha1 - This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907