cancel
Showing results for
Did you mean:
Helper I

## DAX for column total

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!

1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

6 REPLIES 6
Super User

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper I

@mahoneypat That's brilliant! Thanks!

Super User

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)

Helper I

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

Super User

@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/

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Super User

@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

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.

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!