Grand Total is not correct

[bml123]

Hi,

I have a matrix like this where amount is a measure. I am getting grand total incorrect as you can see.  There is some calculation in amount measure for Others.  It's ignoring the value for others.  I want to get 45 grand total.  How do I get the correct grand total?

Employee	Amount
E1	10
E2	20
Others	15
Total	30

[PaulDBrown · Accepted solution]

Which table is the Employee field in the visual coming from?

If it's the Employee table try:

AMOUNT =
SUMX (
    'Employee',
    CALCULATE (
        SWITCH (
            MAX ( Employee[Employee] ),
            "Others", DIVIDE ( 150, 10 ),
            DIVIDE ( [Target], [Num_weeks] )
        )
    )
)

[Samarth_18]

Hi @bml123 ,

Create a another measure like this:-

measure= sumx(summarize('table','table'[employee],"Correct_Sum",[amount]),[Correct_Sum]))

 Thanks,

Samarth

[PaulDBrown]

Try:

Total amount = SUMX(Employee, [Amount])

[bml123]

Hi @PaulDBrown 

I have tried your measue, it is not giving any values. It's just blank. 

I want the grand total in my matrix to have the correct value. 

[PaulDBrown · Accepted solution]

Which table is the Employee field in the visual coming from?

If it's the Employee table try:

AMOUNT =
SUMX (
    'Employee',
    CALCULATE (
        SWITCH (
            MAX ( Employee[Employee] ),
            "Others", DIVIDE ( 150, 10 ),
            DIVIDE ( [Target], [Num_weeks] )
        )
    )
)

[bml123 · Accepted solution]

Hi @PaulDBrown ,

It worked. Thank you so much for the solution. 

[bml123]

Employee is coming from employee table

[mh2587]

Would you share the amount measure?

[bml123]

@mh2587 

Here is the formula of my Amount measure

Amount = SWITCH(CALCULATE(MAX(Employee[Employee])),
"Others",CALCULATE(150/10),
CALCULATE([Target] / [Num_weeks]))