Forum Discussion

RubenIdx's avatar
RubenIdx
Helper I
6 years ago
Solved

Row result filtering

I have elaborated a model of which I have a situation with the measure "% Var Total" this measure has two behaviors one at the detail level (product) and the other in the subtotals depending on the aggregations used.

Taking as good the result of the calculation in my matrix, articles are shown that although they had movement do not correspond to the filtered period, for example the product "Item1097" had movements in March 2018 when I am comparing 2020 vs 2019.

Can you tell me what is the correct way to filter this result?

 

I appreciate your guidance in advance

 

Here is the model download link

 

https://drive.google.com/file/d/1uIlxPNASRaix02481wKpbFK3fwj9HsHy/view?usp=sharing 

 

 

 

The measure in question:

 

% Var Total = Var TsVarTotal = DIVIDE(CALCULATE(Ventas[Varia Total]),CALCULATE([Venta Anterior]),0) 
VAr X = 1

 Var ProdVaria= CALCULATE([Varia Total Z])
Return 
SWITCH(TRUE(),
       HASONEVALUE(Productos[Producto]),ProdVaria,
       HASONEVALUE(Clases[Desc Clase]),TsVarTotal,            	
       HASONEVALUE(DivRes[DivShr]),TsVarTotal, 
       HASONEVALUE(Ventas[SECTOR]),TsVarTotal,                                 	
       HASONEVALUE(Clientes[Holding]),TsVarTotal, 
       HASONEVALUE(Vendedores[Name Vendedor]),TsVarTotal, 
       HASONEVALUE(Monedas[Currency]),TsVarTotal,
       BLANK()
)

 

 

  • Hi RubenIdx ,

     

    If you don't want to see blank and 0 data, you could configure fields it in the filter pane.

    You could use ISINSCOPE() function to keep the orginal total value.

     

     

3 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support

    Hi RubenIdx ,

     

    I still don't understand your issue totally, you could show your expected result here.

     

     

    • RubenIdx's avatar
      RubenIdx
      Helper I

      Hi!, thanks for the attention.

       

      The expected result is to avoid or hide rows with no data in the selected periods for example in the next image the rows into the red square has no data because these items had sales along 2018 or among april 2019 and december 2019 and Im comparing jan-march 2019 vs jan-march 2020.

       

      And this is the result I would like to achieve.

       

      Thanks in advance.

       

      • v-eachen-msft's avatar
        v-eachen-msft
        Community Support

        Hi RubenIdx ,

         

        If you don't want to see blank and 0 data, you could configure fields it in the filter pane.

        You could use ISINSCOPE() function to keep the orginal total value.