topic Re: DAX for column total in DAX Commands and Tips

DAX for column total

hcze — Tue, 25 Aug 2020 06:20:24 GMT

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!

Re: DAX for column total

Greg_Deckler — Tue, 25 Aug 2020 01:13:55 GMT

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/963588#M428

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.

Re: DAX for column total

amitchandak — Tue, 25 Aug 2020 02:19:13 GMT

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/

Re: DAX for column total

Anonymous — Tue, 25 Aug 2020 13:43:09 GMT

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)

Re: DAX for column total

hcze — Tue, 25 Aug 2020 22:57:33 GMT

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

Re: DAX for column total

mahoneypat — Wed, 26 Aug 2020 01:18:24 GMT

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

Re: DAX for column total

hcze — Wed, 26 Aug 2020 01:58:17 GMT

mahoneypat That's brilliant! Thanks!