Forum Discussion
Helper IIDAX 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
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!
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
6 Replies
- mahoneypat
Microsoft Employee
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
hczemahoneypat That's brilliant! Thanks!
- Greg_Deckler
Community Champion
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. - amitchandak
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/
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
Anonymous it was to have total column as first column. Unless you have other trick to get there?
