Forum Discussion

afrutos's avatar
afrutos
Icon for Resolver I rankResolver I
4 years ago
Solved

Matrix doesn`t add totals correctly when using slicer

Hi, good morning, i`m stocked...
Can anybody help me?


I need to get a matrix, that shows me for each month, which cinemas there are and their amount depending on whether it is in M or E or both, and a column of the difference of M-E.

 

To calculate M I have done it in the following way:
MINISTRY = CALCULATE(SUM(Query1[Amount]),Query1[ORIGIN]="MINISTRY").

To calculate E I have done it in the following way:
EXTERNAL = CALCULATE(SUM(Query1[Amount]),Query1[ORIGIN]="EXTERNAL")

And this is the difference:
DIFFERENCE M-E = Measures[MINISTRY]-Measures[EXTERNAL].

 

At the same time I have a segmenter that:
in our favor= DIFFERENCE M-E < 0
against=DIFFERENCE M-E > 0
no difference = M-E DIFFERENCE = 0


When I apply the slicer, it filters the matrix correctly by the corresponding amounts, but it does not perform the sum correctly because it keeps the total as if it had not filtered anything.

 

 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi afrutos ,

     

    You can try to use virtual tables in measure to guarantee the calculation results. Something like this:

     

    example =
var _s = 
SUMMARIZE (
    Query1,
    [Mes],
    "m", CALCULATE ( SUM ( Query1[Amount] ), Query1[ORIGIN] = "MINISTRY" ),
    "e", CALCULATE ( SUM ( Query1[Amount] ), Query1[ORIGIN] = "EXTERNAL" )
)
return
sumx(_s,[m]-[e])

     

    Please provide some example data and the expected results, if you need more help.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi afrutos ,

     

    You can try to use virtual tables in measure to guarantee the calculation results. Something like this:

     

    example =
var _s = 
SUMMARIZE (
    Query1,
    [Mes],
    "m", CALCULATE ( SUM ( Query1[Amount] ), Query1[ORIGIN] = "MINISTRY" ),
    "e", CALCULATE ( SUM ( Query1[Amount] ), Query1[ORIGIN] = "EXTERNAL" )
)
return
sumx(_s,[m]-[e])

     

    Please provide some example data and the expected results, if you need more help.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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