Cost breakdown and changing of filtered value

[Verkana]

Hello,

I kindly request help with my problem.

I need to define a measure that will be multiplied by another measure if the value of another field is empty and the filtered field will no longer empty, but will have value.

Or if there is another way, maybe in power query or editing „Accounting_data“ by adding columns –or something else, I will be thankful.

I’ll try to explain more specifically.

I have for example accounting data in table „Accounting_data“:

Date	Label	RefCin	Value
1.4.2022	Marketing cost		250,-
6.6.2022	Rent	1	380,-
5.5.2022	Wages	2	220,-
7.7.2022	Wages	3	150,-
1.2.2022	Rent		200,-
3.3.2022	IT services		450,-
4.4.2022	Wages		100,-

Then I have measure „Percentage“ (counted from another table).

RefCin-percentage	Percentage
1	15 %
2	25 %
3	60 %

Here is the quest:

I need to make measure for every RefCin:

1. If the RefCin is filled by number (1,2,3) :

• make sum of „Values“.

1. But if the RefCin is empty, than

• make sum of „Values“ where RefCin=0,
• multiply by measure „Percentage“
• Change RefCin (empty) to „RefCin“ (1,2,3)
• Add to sum of values for RefCin in Step 1

Result:

RefCin-percentage	Year costs
1	380 + 0,15*(250+200+450+100) = 530
2	220 + 0,25*(250+200+450+100) = 470
3	150 + 0,6*(250+200+450+100) = 750

[Anonymous · Accepted solution]

Hi @Verkana ,

My Data model:

M_Percentage = CALCULATE(SUM('Table'[Percentage]))

Measure:

Measure = 
VAR _VALUE = CALCULATE(SUM(Accounting_data[Value]),FILTER(ALL(Accounting_data), Accounting_data[RefCin] =MAX('Table'[RefCin])))
VAR _BLANK = CALCULATE(SUM(Accounting_data[Value]),FILTER(ALL(Accounting_data), Accounting_data[RefCin] = BLANK()))
RETURN
_VALUE + [M_Percentage] * _BLANK

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.