Forum Discussion

Raul's avatar
Raul
Icon for Post Patron rankPost Patron
9 years ago
Solved

Apply filter to a measure

Hello forum!

I need your help for the following measure. I want to break down the total of a column by the values of the records that compose it in a matrix visualization:

 

 

For exemple: the total of column FACT must be distributed between the records for the TIPUS COLABORADOR column. I created a new measure called DESFACT with the formula of the image but the total value is not the same that the column FACT and the distrubution is not correct either. The Total columns on the right are correct and the distribution is correct too, but for every type of document (column TIPUS with de BONO and EXPED values) are not correct. What is wrong? I don't know how to filter on the formula of the new measure.

 

This is the DAX formula:

DESFACT =
   VAR TotalFAC = CALCULATE(SUM(Tabla1[FACT]);ALLSELECTED(Tabla1))
   VAR TotalCOST = CALCULATE(SUM(Tabla1[COST]);ALLSELECTED(Tabla1))
   RETURN
       (TotalFAC * SUM(Tabla1[COST]))/ TotalCOST

 

Thanks!

Need Help
  • Raul's avatar
    Raul
    9 years ago

    I found the solution. The measure must have the following formula:

     

    DESFACT =
      VAR TotalFAC = CALCULATE(SUM(Tabla1[FACT]);ALLSELECTED(Tabla1[COLABORADOR]))
      VAR TotalCOST = CALCULATE(SUM(Tabla1[COST]);ALLSELECTED(Tabla1[COLABORADOR]))
    RETURN
       (TotalFAC * SUM(Tabla1[COST]))/ TotalCOST

     

    Thanks MFelix for your help!

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    9 years ago

    Hi Raul,

     

    try to change you formula to something like this:

     

    DESFACT =
VAR TotalFAC =
    CALCULATE (
        SUM ( Tabla1[FACT] );
        ALLSELECTED ( Tabla1 );
        FIRSTNONBLANK ( Tabla1[TIPUS]; SUM ( Tabla1[FACT] ) )
    )
VAR TotalCOST =
    CALCULATE (
        SUM ( Tabla1[COST] );
        ALLSELECTED ( Tabla1 );
        FIRSTNONBLANK ( Tabla1[TIPUS]; SUM ( Tabla1[FACT] ) )
    )
RETURN
    ( TotalFAC * SUM ( Tabla1[COST] ) )
        / TotalCOST

    Check if it works, did not tried in the computer.

     

    Regards,

    MFelix

    • Raul's avatar
      Raul
      Icon for Post Patron rankPost Patron
      9 years ago

      Thanks for your answer MFelix,

      Now, the totals for the columns FACT and DESFACT have the same value but don't appear the distribution for the column COLABORADOR and the Total colum of DESFACT it's not correct, like this:

       

       

      I need the DESFACT column to distribute the total for the records in the COLABORADOR column and that the total is the correct one.

       

      This is a summary of the table source of the Power BI file:

       

      COLABORADORCOSTFACTURACIOTIPUS
        2514,85BONO
      MR116,5 BONO
      RC200 BONO
      SM609,7 BONO
        1481,92EXPED
      MR100 EXPED
      RC305 EXPED
      SM79,5 EXPED

       

      Thank you.

       

       

      • Raul's avatar
        Raul
        Icon for Post Patron rankPost Patron
        9 years ago

        I found the solution. The measure must have the following formula:

         

        DESFACT =
          VAR TotalFAC = CALCULATE(SUM(Tabla1[FACT]);ALLSELECTED(Tabla1[COLABORADOR]))
          VAR TotalCOST = CALCULATE(SUM(Tabla1[COST]);ALLSELECTED(Tabla1[COLABORADOR]))
        RETURN
           (TotalFAC * SUM(Tabla1[COST]))/ TotalCOST

         

        Thanks MFelix for your help!