SUM for Filtered Values

[CiuCiCiao]

Hi guys,

I am definitely not a programmer, but I have to find a way out of this... I would really appreciate some help!

I have the table Cost which contains cumulative costs of production.

Performance	Material	HR	Staff
Performance1	-	5.000	-
Performance2	-	-	-
Performance3	-	4.000	-
Performance4	-	-	-
Performance5	-	3.000	-
Performance6	-	-	-
N/A	30.000	-	10.000

I have the table Revenue where each row represent a stream of production.

Performance	Time
Performance2	3
Performance6	7
Performance2	23
Performance1	7
Performance5	88
Performance5	34
Performance3	90

I have the aggregated Cost that I want to spread for each line in Revenue as a function of the production time Revenue[Time].

So I need to do Cost*(Time/TotalTime). Then I realised that I need to divide the TotalTime for each Performance.

So my idea was the following:

=RELATED(Cost[Material])* Revenue[Time]/CALCULATE(SUM(Revenue[Time]),FILTER(Revenue, Revenue[Performance]="Performance1"))

This formula is working good for Performance1 but is still multipling the other performance for a fraction of the other values...

Any help?

Thanks

M.

[CiuCiCiao]

[v-yuezhe-msft]

@CiuCiCiao,

Based on your description and the above formula , I am not quite clear about your expected result. Do you want to calculate the result for each performance using the logic: Cost[Material] of each performance *(Time of each performance/ total time of all performance)? For example, for performance3, you want to get result: Cost[Material] of performance3 *(90/(3+7+23+7+88+34+90)), right? If that is the case, please use the following DAX.

Column= RELATED(Cost[Material])* Revenue[Time]/CALCULATE(SUM(Revenue[Time]),ALL(Revenue))

If the above formula doesn't help, please post your desired result based on above sample data.

Regards,

[CiuCiCiao]

@v-yuezhe-msft

First thanks for your reply!

I need the denominator to be the total time of each performance.

The formula would be Cost[Material] of each performance *(Time of each performance/ total time of each performance).

For the above example would be:

Cost[Material] of performance3 *(90/(SUM(Performance3)))
OR
Cost[Material] of performance5 *(88/(SUM(88+34)))

I guess my real issue is that i am not able (in excel terms) to filter an entire column A and SUM only the filtered values of column B.

Something like:

(Column B Row / (Filter Column A for a Value and SUM Column B remaining)) ---> Then multiply for the filtered Column A Value of Another table (Cost)

Thanks

M.

[v-yuezhe-msft · Accepted solution]

@CiuCiCiao,

Create your column using the following DAX.

Column = RELATED(Cost[Material])* Revenue[Time]/ CALCULATE(SUM(Revenue[Time]),FILTER(Revenue,Revenue[Performance]=EARLIER(Revenue[Performance])))

Regards,

[CiuCiCiao]

Thanks a lot is working perfectly, now I just have to understand why

Thanks Again!