Forum Discussion

wmoura12's avatar
wmoura12
Regular Visitor
8 years ago
Solved

How can I divide a column by a fixed value

How can I divide a column by a fixed value

 

I´m in trouble with an issue that seems to be easy to solve, but I can´t do it. I have a Matrix and i need to create a measure or a column dividing MOV YTD of the row by MOV YTD OF RECEITA LIQUIDA to calculate the percentage. For Example:

 

 

 

 

 

MOV YTD = CALCULATE(sum(dRazao[VALOR]); DATESYTD(dCalendario[Data]))

 

% Vert_YTD =


VAR Vert_MOV_YTD_01 = CALCULATE([RL YTD]; ALL('dEBITDA'[CONTA_EBITDA]; 'dEBITDA'[#EBITDA]))

VAR Vert_MOV_YTD_02 = CALCULATE([MOV YTD];ALL('dRazao'[CLASSECONTA2]);ALL('dRazao'[ORDEMDRE]))

RETURN DIVIDE([MOV YTD];IF(DIVIDE ([MOV YTD] ; Vert_MOV_YTD_02) = 1 ; if(ISBLANK(Vert_MOV_YTD_01); Vert_MOV_YTD_02;Vert_MOV_YTD_01) ;Vert_MOV_YTD_02))

 

 

WALLACE

 

  • wmoura12's avatar
    wmoura12
    8 years ago

    Maybe you can change the table

    Medida = Calculate([MOV YTD];ALL(dEBITDA);Filter(dEBITDA;dEBITDA[CONTA_EBITDA]="RECEITA LIQUIDA");dRazao)

     

    The number has changed but it is wrong.

     

    After several attempts, I succeeded

     

    %Vert RLiq =

    VAR DEN_01 = CALCULATE([RL YTD];ALLSELECTED(dRazao); FILTERS(dColigada[Empresa_Simples]))
    VAR NUM_01 = [MOV YTD]

    RETURN
    DIVIDE(NUM_01;DEN_01;0)

     

8 Replies

  • Floriankx's avatar
    Floriankx
    Solution Sage
    8 years ago

    Hello,

     

    you can use something like:

     

    Calculate([MOV YTD];ALL(Table):Filter(Table;Conta EBITDA="RECEITA LIQUIDA"))

     

    Without knowing the structure of your raw data it is hard to give precise support.

     

    Best regards.

    • wmoura12's avatar
      wmoura12
      Regular Visitor
      8 years ago

      Thank  you for your help!

      I´d already tried this way. 

      Sorry about the model. I forgot to post it. The measure i created based on your suggestion respected the context and calculated the same value ou MOV YTD measure. Follow the diagram and the result of your suggestion.

      I need, at this case, to repeat 63.368.009 at all lines to divide by.

       

      Medida = Calculate([MOV YTD];ALL(dEBITDA);Filter(dEBITDA;dEBITDA[CONTA_EBITDA]="RECEITA LIQUIDA"))

       

       

      • Floriankx's avatar
        Floriankx
        Solution Sage
        8 years ago

        Hello there are two things you could try next:

         

        Firstly, add ALL(dColigada) to your CALCULATE. I assume this is your second level row.

         

        If this doesn't work you can try and build the Measures in two steps.

        Step1:=Calculate([MOV YTD];Filter(...))

        Step2:=Calculate([Step1];ALL(...);All(...))

         

        Additionally you could highlight in your image which row is related to which row in your dRazzo.

        And please show us the columns you selected for your Pivot.

         

        Best regards