Apply filter to a measure

[Raul]

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!

[MFelix]

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]

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:

COLABORADOR	COST	FACTURACIO	TIPUS
		2514,85	BONO
MR	116,5		BONO
RC	200		BONO
SM	609,7		BONO
		1481,92	EXPED
MR	100		EXPED
RC	305		EXPED
SM	79,5		EXPED

Thank you.

[Raul · Accepted solution]

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!