Forum Discussion

hcze's avatar
hcze
Icon for Helper II rankHelper II
6 years ago
Solved

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, Tbl...
DAX
DAX Calculation
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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/