Dax measure not showing total in table

[viralpatel21]

Hi all

As you can see that i am not getting the Total by rows for my other 4 measure? I did check in and total by row is on.

This is one of my measures:

Total Closed = 

var Cumula=

CALCULATE(

[Number of Customers (Closed)],

FILTER(ALL(FeeDate[Date]),

FeeDate[Date] <= MAX(FeeDate[Date])))

return

IF (

AVERAGE ( FeeDate[Date] ) > TODAY(),

BLANK(),

Cumula

)

Please could you help me

Thanks

Viral

[viralpatel21]

@v-janeyg-msft @amitchandak 

thanks for the reply.

Related to this. I have the same issue where the months Total are correct but if i bring like a product and total it up by month its not correct.

i.e. Wrong total for the brands. The Brand is a column in the contacts table and the values is a measure.

 In total i have converted GBP and EUR to USD using a measure Like so. both are similar measure

Payment GBPConversion1 =

var PaymentBalAdjustmentGBP =

CALCULATE(

[BalAdjValue],

FILTER(BalanceAdjustments,BalanceAdjustments[Currency] = "GBP"),

FILTER(BalanceAdjustments,BalanceAdjustments[BalAdjType] ="WTT" ||

BalanceAdjustments[BalAdjType] = "WTTR"),

USERELATIONSHIP(BalanceAdjustments[Date],FeeDate[Date] ))

var convertgbp = CALCULATE(

SUM('FX Rates'[GBP_Avg]) * PaymentBalAdjustmentGBP,

USERELATIONSHIP(BalanceAdjustments[Date],FeeDate[Date]),

USERELATIONSHIP(FeeDate[Months],'FX Rates'[Months])

)

var previousmonthfx = CALCULATE(

SUMX('FX Rates','FX Rates'[GBP_Avg]),

PREVIOUSMONTH(FeeDate[Date])

)

var previousmth = CALCULATE(VALUE(

PaymentBalAdjustmentGBP * previousmonthfx),

USERELATIONSHIP(BalanceAdjustments[Date],FeeDate[Date]),

USERELATIONSHIP(FeeDate[Months],'FX Rates'[Months])

)

return

convertgbp

and this is the final total measure: 

Payment USDTotal =

var GBPConversion = SUMX(VALUES(FeeDate[Months]),[Payment GBPConversion1])

var EURConversion = SUMX(VALUES(FeeDate[Months]),[Payment EUR Conversion1])

return

[Payment BalAdjustment USD] + GBPConversion + EURConversion

But my total is still wrong?

could you help

thanks

viral

[v-janeyg-msft]

Hi, @viralpatel21 

Since you can calculate the result, it means that the syntax of the code is no problem. The key lies in the context of the measure, which determines the value of the result.

Can you share some sample fake data and your desired result? We need more details to help you.

Best Regards

Janey Guo

[v-janeyg-msft · Accepted solution]

Hi, @viralpatel21 

You can easily add a measure to sum.

Like this:

measure =
SUMX ( FeeDate, [Total Closed] )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[viralpatel21]

@v-janeyg-msft 

Thanka you for the reply I will try this out. Could you explain why i would need to add a new measure with a sumx?

Thanks

viral

[v-janeyg-msft]

Hi, @viralpatel21 

Because you use if function, this will affect the calculation of the default 'total' in visual. You can also use summarize function to solve the problem if it doesn't work.

Actually I don’t really understand why you use avg to compare dates, but if you can get the data you want, then it’s okay.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[viralpatel21]

@v-janeyg-msft 

I have that average there because what is happening the other months values are being copied over. I have shared a image. How could i fix that and also get the correct total? From July onwards the values are being copied from June and i want it to display blank.

thanks

viral

[v-janeyg-msft]

Hi, @viralpatel21 

Can you explain what your average means?

[viralpatel21]

Hi 

Sorry i have used the average formula slightly wrong. I was trying to show blank result from June onwards.

If i delete that average formula out and just return Cumula

then from july onwards its copying data from June(as we are currently in June) and that is wrong.

[v-janeyg-msft]

Hi, @viralpatel21 

I asked you before, what kind of result do you want, I don’t care where you are wrong, I want to know your calculation logic.

If you want the data after June to be blank, you can add a condition to the original expression:measure 2 =
IF ( table[date] < DATE ( 2021, 7, 1 ), [measure] ).

Best Regards

Janey Guo

[viralpatel21]

Hi Sorry for the confusion,

Let me start over:

This is the formula that i have created to calcualte the cumulative of Active customers for 2021.

Cumulative =

CALCULATE(

[Number of Customers (Active)],

FILTER(ALLSELECTED(FeeDate[Date]),

FeeDate[Date] <= MAX(FeeDate[Date])))

And this is the result

I would like to the Cumulative figure as of Today (21/06/2021 or June) but in the table is showing a figure for July, August, September, October, November and December.

like so :

I hope that made sense.

thanks

viral

[v-janeyg-msft]

Hi, @viralpatel21 

Try:

Cumulative =
VAR a =
    CALCULATE (
        [Number of Customers (Active)],
        FILTER ( ALLSELECTED ( FeeDate[Date] ), FeeDate[Date] <= MAX ( FeeDate[Date] ) )
    )
RETURN
    IF ( MAX ( FeeDate[Date] ) > EOMONTH ( TODAY (), 0 ), BLANK (), a )

I don’t know your context and data. If the result is wrong, I need you to show me more details about data and context.

Best Regards

Janey Guo

[amitchandak]

@viralpatel21 , Try like

var Cumula=
CALCULATE(
[Number of Customers (Closed)],
FILTER(ALL(FeeDate[Date]),
FeeDate[Date] <= MAX(FeeDate[Date]) && FeeDate[Date] <=today()))
return
Cumula