Multiply 2 Measures by Row

[justivan]

I have 2 measures that determines the Total Cost and the Payout %. Total Cost is straight forward as to Payout%, the value is determined by which Tier is achieved already. If Tier 1 is achieve correspending Payout% is return, if Tier 2 is achieved, corresponding Payout % is returned and so on. Simply multiplying Total Cost and Payout% will return the correct amount (i.e. 696026.79 * 3% is 20880.8). The problem is the total. What Power BI is doing is that it is multiplying the total Payout% Total of Total Cost which is not what I want. The goal is to add all Est. Payout Value instead. I hope im making sense.

Totals

Here are the 2 measures in question. I think the way I retreived the correct Payout% is not the most ideal.

Payout % = 
VAR vT1 =
    MINX ( ReservationList, [Payout1] )
VAR vT2 =
    MINX ( ReservationList, [Payout2] )
VAR vT3 =
    MINX ( ReservationList, [Payout3] )
VAR vT4 =
    MINX ( ReservationList, [Payout4] )
RETURN
    SWITCH (
        TRUE (),
        [Tier 1 %] < 1, 0,
        [Tier 1 %] >= 1 && OR ( [Tier 2 %] = "N/A", [Tier 2 %] < 1 ), vT1,
        [Tier 2 %] >= 1 && OR ( [Tier 3 %] = "N/A", [Tier 3 %] < 1 ), vT2,
        [Tier 3 %] >= 1 && OR ( [Tier 4 %] = "N/A", [Tier 4 %] < 1 ), vT3,
        [Tier 4 %] >= 1, vT4
    )

Est. Payout Value = 
[Total Cost] * [Payout %]

This is what the table for Tier and Payout looks like

 

[v-yingjl · Accepted solution]

Hi @justivan ,

Basically it looks like a wrong total issue, you can refer this article:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907 

The EST measute would be like this:

Est. Payout Value =
VAR tab =
    ADDCOLUMNS (
        'Table',
        "Total cost",
            [T1] + [T2] + [T3] + [T4],
        "Payout %",
            VAR vT1 =
                MINX ( 'Table', [Payout1] )
            VAR vT2 =
                MINX ( 'Table', [Payout2] )
            VAR vT3 =
                MINX ( 'Table', [Payout3] )
            VAR vT4 =
                MINX ( 'Table', [Payout4] )
            RETURN
                SWITCH (
                    TRUE (),
                    [Tier 1 %] < 1, 0,
                    [Tier 1 %] >= 1
                        && OR ( [Tier 2 %] = "N/A", [Tier 2 %] < "1" ), vT1,
                    [Tier 2 %] >= "1"
                        && OR ( [Tier 3 %] = "N/A", [Tier 3 %] < "1" ), vT2,
                    [Tier 3 %] >= "1"
                        && OR ( [Tier 4 %] = "N/A", [Tier 4 %] < "1" ), vT3,
                    [Tier 4 %] >= "1", vT4
                )
    )
VAR tb =
    ADDCOLUMNS ( tab, "est", [Total cost] * [Payout %] )
RETURN
    SUMX ( tb, [est] )

You need to add your previous measure's formula into a variable table as a 'fact' column to use the context to calculate.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.