Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a parameter table as shown below:
And all need is to match the calculated cash down % per Location for a year/month with the above cash % limits and return the corresponding $Com (Column H).
I excel it is way easier, in Power BI I tried such a solution below
Is there any simpler way that you can suggest to me
Thank you
Solved! Go to Solution.
Hi @YavuzDuran,
I modify your formula and tried to simplify these expressions, you can try to use the below formula if it helps:
Location Manager - Cash Down % Commission =
VAR filtered =
FILTER (
'Parameters-LocationManager',
'Parameters-LocationManager'[Year-Month-Branch] = SalesCommission[Year-Month-Branch]
)
VAR currIndex = 'Parameters-LocationManager'[Cash% Index]
VAR currCashDown = SalesCommission[Cash Down % - Calculated]
VAR _cashIndex1 =
CALCULATE (
SUM ( 'Parameters-LocationManager'[Cash %] ),
FILTER ( filtered, [Cash% Index] = 1 )
)
RETURN
IF (
currIndex = 1,
IF ( currCashDown < _cashIndex1, 0 ),
IF (
currCashDown
< CALCULATE (
SUM ( 'Parameters-LocationManager'[Cash %] ),
FILTER ( filtered, [Cash% Index] = currIndex )
),
CALCULATE (
SUM ( 'Parameters-LocationManager'[$ Com] ),
FILTER ( filtered, [Cash% Index] = currIndex - 1 )
),
CALCULATE (
SUM ( 'Parameters-LocationManager'[$ Com] ),
FILTER ( filtered, 'Parameters-LocationManager'[Cash% Index] = 5 )
)
)
)
Regards,
Xiaoxin Sheng
Hi @YavuzDuran,
I modify your formula and tried to simplify these expressions, you can try to use the below formula if it helps:
Location Manager - Cash Down % Commission =
VAR filtered =
FILTER (
'Parameters-LocationManager',
'Parameters-LocationManager'[Year-Month-Branch] = SalesCommission[Year-Month-Branch]
)
VAR currIndex = 'Parameters-LocationManager'[Cash% Index]
VAR currCashDown = SalesCommission[Cash Down % - Calculated]
VAR _cashIndex1 =
CALCULATE (
SUM ( 'Parameters-LocationManager'[Cash %] ),
FILTER ( filtered, [Cash% Index] = 1 )
)
RETURN
IF (
currIndex = 1,
IF ( currCashDown < _cashIndex1, 0 ),
IF (
currCashDown
< CALCULATE (
SUM ( 'Parameters-LocationManager'[Cash %] ),
FILTER ( filtered, [Cash% Index] = currIndex )
),
CALCULATE (
SUM ( 'Parameters-LocationManager'[$ Com] ),
FILTER ( filtered, [Cash% Index] = currIndex - 1 )
),
CALCULATE (
SUM ( 'Parameters-LocationManager'[$ Com] ),
FILTER ( filtered, 'Parameters-LocationManager'[Cash% Index] = 5 )
)
)
)
Regards,
Xiaoxin Sheng
@YavuzDuran Generally you would use LOOKUPVALUE or MAXX(FILTER(...),...).
If you stay with your current solution, I highly recommend SWITCH(TRUE(),...) vs nested IF statements.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |