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: ...
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!

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!