Multiply filtered rows and then sum

[Anonymous]

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!!

[Anonymous]

Thanks for your solution.

[MFelix]

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]

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]

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]

Hi @MFelix ,

Thanks you very much for the explanation.

Giving you more conxtext, "Individual" column is related to other table where  Individuals refer to different divisions. Fo example individuals "A" & "B" refer to Division 1 and "C" & "D" refer to Division 2. My original formula worked when selecting just one Individual but not a division. In my page "Individual" & "Division" are slicers.

Trying to adjust your second formula to solve my problem, could you specify which are the formulas for [Trade_Value], [Sales_Value] & [VAT_Value].

Thank you very much for your help, hope with this explanation I can get to the right formula.

Regards,

[MFelix · Accepted solution]

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

[Anonymous]

Thank you very much @MFelix, this perfectly worked!!!

Regards,