DAX for column total

[hcze]

Hi,

I want to have the column total as the first column in a matrix but the total does not show up (blank) for some reasons.

As a background, there are three tables involved: TblCategory, TblYear, Facts which have relationship amongst them. There also slicers for incoming filters,

My current DAX is

Amount  =

VAR CY = SELECTEDVALUE(TblYear[Sales Year])

RETURN

IF (CY = "All Years",

SUM(Facts[Sales]),

CALCULATE(SUM(Facts[SalesI]),TblYear[Sales Year]=CY)

)

Following is the expected result (and it needs to take into account slicers as well)

Category	All Years	2020	2019	2018
Books	290	100	80	110
Clothes	590	200	210	180
Toys	300	120	100	80

TblYear

2018

2019

2020

All Years

TblCategory

Books

Clothes

Toys

Facts

Books	2018	110
Books	2019	80
Books	2020	100
Clothes	2018	180
Clothes	2019	210
Clothes	2020	200
Toys	2018	80
Toys	2019	100
Toys	2020	120

Thanks for helping!

[mahoneypat · Accepted solution]

Here is how you can do this one.

First, add an index to your TblYear table so you can use it as a Sort By Column to get All Years to show before the years.

Then use this measure.

NewMeasure =
VAR thisyeartotal =
SUM ( Facts[Amount] )
RETURN
IF (
ISBLANK ( thisyeartotal ),
CALCULATE ( SUM ( Facts[Amount] ), ALL ( TblYear ) ),
thisyeartotal
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[hcze]

@mahoneypat That's brilliant! Thanks!

[harshnathani]

HI @hcze ,

You can remove 'All Years' from your Year Table.

This is how your model will look.

In the slicer you have an option to Select All. Use that option incase you want to get the values of All Years.

Use values from the different tables to show in a Matrix.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[hcze]

@harshnathani  it was to have  total column as first column. Unless you have other trick to get there?

[amitchandak]

@hcze ,If TblYear is connected to Facts then it will filter automatically in this case. If not filter need be move fact[Year] or year(fact[date])

example

Amount =
VAR CY = SELECTEDVALUE(TblYear[Sales Year])
RETURN
IF ( not(isfiltered(TblYear[Sales Year])),
SUM(Facts[Sales]),
CALCULATE(SUM(Facts[SalesI]),filter(fact, year(fact[date]) =CY))
)

isfiltered can be used to check if there any value selected or not

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[Greg_Deckler]

@hcze - Difficult to get a handle on exactly what is wrong without source data. But, seems like you are trying to add a measure into your matrix and so you may need The New Hotness - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

But, hard to say, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.