Forum Discussion
Multiply 2 Measures by Row
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
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.
1 Reply
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.