Forum Discussion

afrutos's avatar
afrutos
Resolver I
4 years ago
Solved

INCORRECT TOTAL IN MATRIX

Hello good morning team,
How is it going?
I'm stuck with a measure, because I can't get the right total in my matrix.
I am trying to apply the same measure, but changing the level of hierarchy so that the total applies well to me, however I do not get the expected result. 

 

Need Help
Show and Tell
  • afrutos's avatar
    afrutos
    4 years ago

    In the end I needed to change the data at the source 
    otherwise it was impossible for me because I had to take into account many factors that were not well collected.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    afrutos , With help from columns in the visual group by, have new measure

     

    Sumx(Summarize(Table, Table[NomEmpresa], Table[NombreCine], "_1", [MiniSTERIO_PRUEBA2]) , [_1])

     

    and use that in visual

    • afrutos's avatar
      afrutos
      Resolver I
      4 years ago

      It is not working, let me explain the case: 

      In short, what I was looking for was that depending on my segmenter:
      IN FAVOUR (DIF M-E<0)
      AGAINST (DIF M-E<0)
      NO DIFFERENCES (DIF M-E=0)

      If I filtered through those lines whose difference is against us that I showed the months -->cines with their amounts for both the ministry column and the external column and their totals

      And create that measure for each column in my matrix.

      To create the segmenter,
      Create an isolated table for me
      And then for each column I applied that.

       

      MINISTERIO = CALCULATE(SUM(Consulta1[Importe]),Consulta1[ORIGEN]="MINISTERIO")
      EXTERNO = CALCULATE(SUM(Consulta1[Importe]),Consulta1[ORIGEN]="EXTERNO")
      DIFERENCIA M-E = [MINISTERIO]-[EXTERNO]
       
      In order to calculate the total I have done this function so that the segmenter picks me up. But when I go down in level, it doesn't work

      MINISTERIO FILTRO = var estado=SELECTEDVALUE(SEGMENTADOR[Columna2])
       
      return
      SWITCH(true(),
       
      estado=1, SUMX(VALUES(Consulta1[IdEmpresa]), if([DIFERENCIA M-E]<0,[MINISTERIO],BLANK())),
      estado=2,SUMX(VALUES(Consulta1[IdEmpresa]), if([DIFERENCIA M-E]>0,[MINISTERIO],BLANK())),
      estado=3, SUMX(VALUES(Consulta1[IdEmpresa]), if([DIFERENCIA M-E]=0,[MINISTERIO],BLANK())), [MINISTERIO])
       
      Calculates well at the row level but does not correctly perform the column-level total
    • afrutos's avatar
      afrutos
      Resolver I
      4 years ago

      In the end I needed to change the data at the source 
      otherwise it was impossible for me because I had to take into account many factors that were not well collected.