Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Multiply filtered rows and then sum

Hi all, 

 

I want to calculate this formula on Power BI:

Days = (Trade) / (Sales * VAT)

 

In my the same table (FCF), I have the values for the previous concepts as follows:

 

Concept             Value              Individual

 

Trade                 35                    A

Sales                  12                    A

VAT                  0.12                   A

Trade                 40                    B

Sales                  10                    B

VAT                  0.15                   B

 

So, I am using the following formulas:

Numerator = SUMX(FILTER('FCF'; 'FCF'[Concept] = "Trade"); 'FCF'[Value])

Denominator Part 1 =  SUMX(FILTER('FCF'; 'FCF'[Concept] = "Sales"); 'FCF'[Value])

Denominator Part 2 = SUMX(FILTER('FCF'; 'FCF'[Concept] = "VAT"); 'FCF'[Value])

Denominator Total = [Denominator Part 1] * [Denominator Part 2]

Days = [Numerator] / [Denominator Total]

 

So, the formulas operate correctly when selecting just one individual. For example, selecting "A". Calculations are:

35 / (12 * 0.12) = 24.3

However, if I select two individuals such as "A" & "B", calculations are incorrect because my formulas sum each concept and them multiply. 

My formulas operate (35 + 40) / ((12 + 10) * (0.12 + 0.15) = 12,62.

And, the correct should be: 

(35 + 40) / ((12 * 0.12) + (10 * 0.15) = 25.51

 

How can I change my formulas to get the right result?

 

Thank you very much for your help in advance!!

 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    As I refer previously the calculation are based on temporary tables the  [Trade_Value], [Sales_Value] & [VAT_Value] are the columns of the temporary table that sum the value of the Vat, sales and Concept. To have what you need you can add the columns you want to give context to the temporary table:

     

    Days =
SUMX (
    SUMMARIZE (
        FCF;
        FCF[Individual];
        "Trade_Value"; CALCULATE ( SUM ( FCF[Value] ); FCF[Concept] = "Trade" )
    );
    [Trade_Value]
)
    / SUMX (
        SUMMARIZE (
            FCF;
            FCF[Individual];
                FCF[Division];
            "Sales_Value"; CALCULATE ( SUM ( FCF[Value] ); FCF[Concept] = "Sales" );
            "VAT_Value"; CALCULATE ( SUM ( FCF[Value] ); FCF[Concept] = "VAT" )
        );
        [Sales_Value] * [VAT_Value]
    )

    Being the columns related with the slicers this should work correctly. 

     

    If this does not work can you share a sample file please, if information is senstitive make it trough private message.

     

    Regards,

    MFelix

7 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi Anonymous ,

     

    The SUMX operator makes the calculation row by row that is why when you are calculating the overall total you are getting the SUM of the values and multiplying by the final value.

     

    Believe that your best option would be to unpivot the Concept column and then make your measure SUMX calculation based on the other columns:

    Days =
SUM ( 'FCF (PIVOT)'[Trade] )
    / SUMX ( 'FCF (PIVOT)'; ( 'FCF (PIVOT)'[Sales] * 'FCF (PIVOT)'[VAT] ) )

    If you want to keep the table has is you should redo your calculation to:

     

    Days =
SUMX (
    SUMMARIZE (
        FCF;
        FCF[Individual];
        "Trade_Value"; CALCULATE ( SUM ( FCF[Value] ); FCF[Concept] = "Trade" )
    );
    [Trade_Value]
)
    / SUMX (
        SUMMARIZE (
            FCF;
            FCF[Individual];
            "Sales_Value"; CALCULATE ( SUM ( FCF[Value] ); FCF[Concept] = "Sales" );
            "VAT_Value"; CALCULATE ( SUM ( FCF[Value] ); FCF[Concept] = "VAT" )
        );
        [Sales_Value] * [VAT_Value]
    )

    This will force your calculation to only get the sales vat and trade for each of the individuals.

     

    Regards,

    MFelix

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi MFelix ,

       

      Thanks for your wuick reply and recommendations.

       

      Regarding the 1st option, I cannot unpivot "Concept" column as there are many other columns on my table.

       

      Regarding the 2st option:
      - I would like not to include in the formula the "Individual" selection, but with an external slider that filters every visualization in the page.

      - I tried to adjust your formula eliminating the "Individual" selection but it didn´t work (see below). The problem might be that I do not really know what you refer to [Trade_Value], [Sales_Value] and [VAT_Value] measures.

       

      Days =
SUMX (
    CALCULATE ( SUM ( FCF[Value] ); FCF[Concept] = "Trade" )
    );
    [Trade_Value]
)
    / SUMX (
        CALCULATE ( SUM ( FCF[Value] ); FCF[Concept] = "Sales" );
            "VAT_Value"; CALCULATE ( SUM ( FCF[Value] ); FCF[Concept] = "VAT" )
        );
        [Sales_Value] * [VAT_Value]
    )

       

      I would very much appreciate your help on this. Thanks a lot in advance.

       

      Regards, 

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago
        Hi Anonymous,

        The calculation that I make is a summarization of the original table to make the calculation, the fact that I have the Individual on the calculation is only to make the sum working for each part of your calculation.

        As a quick explanation I am calculating a virtual table with individual summing the Trade value and then dividing by another virtual table calculating the vat and sales value to make the division. On both of them the individual is the aggregator of the virtual table.

        You can make other aggregator if you have other columns that you need to have on the calculation but that will only give details to make the final result.

        Being a measure you can filter out the result by any slicer you need.

        Regards,
        MFelix
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks for your solution.