cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hcze
Helper I
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)

CategoryAll Years202020192018
Books29010080110
Clothes590200210180
Toys30012010080

 

TblYear

2018
2019
2020
All Years

 

 

TblCategory

Books
Clothes
Toys

 

 

Facts

Books2018110
Books201980
Books2020100
Clothes2018180
Clothes2019210
Clothes2020200
Toys201880
Toys2019100
Toys2020120

 

Thanks for helping!

 

 

 

 

1 ACCEPTED SOLUTION
mahoneypat
Super User
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
)

 

 

mahoneypat_0-1598404552736.png

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
mahoneypat
Super User
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
)

 

 

mahoneypat_0-1598404552736.png

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

@mahoneypat That's brilliant! Thanks!

harshnathani
Super User
Super User

HI @hcze ,

 

 

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

 

This is how your model will look.

 

1.jpg

 

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

 

 

 

2.JPG

 

 

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

 

 

3.JPG

 

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

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

 

amitchandak
Super User
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/

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!
Greg_Deckler
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

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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.