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. P...
  • 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,

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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,

    • CiuCiCiao's avatar
      CiuCiCiao
      Helper I
      9 years ago

      Thanks a lot is working perfectly, now I just have to understand why :manvery-happy:

      Thanks Again!