Forum Discussion

CiuCiCiao's avatar
CiuCiCiao
Helper I
9 years ago
Solved

SUM for Filtered Values

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.

PerformanceMaterialHRStaff
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.

PerformanceTime
 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.

  • Anonymous's avatar
    Anonymous
    9 years ago

    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,

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        CiuCiCiao
        Helper I
        9 years ago

        Anonymous

         

        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.