Forum Discussion

bml123's avatar
bml123
Icon for Post Patron rankPost Patron
4 years ago
Solved

Grand Total is not correct

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?

 

EmployeeAmount
E110
E220
Others15
Total30
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    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] )
        )
    )
)



8 Replies

    • bml123's avatar
      bml123
      Icon for Post Patron rankPost Patron
      4 years ago

      mh2587 

      Here is the formula of my Amount measure

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

    • bml123's avatar
      bml123
      Icon for Post Patron rankPost Patron
      4 years ago

      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's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi bml123 ,

     

    Create a another measure like this:-

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

     

     Thanks,

    Samarth