Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a summary table that will display which tier the total revenue is currently at (i.e., Tier 1) and a matrix table below to show the incentive. The incentive table should be taking revenue*premium% or revenue*non-premium%, depending on which tier I'm at now.
For example, currently I'm at Tier 1, therefore the first row of incentive table should be 8*3% (premium), second row should be 3.5*2% (non-premium).
Does anyone know how I can achieve that? Attached the link to the sample PBIX for reference.
https://www.dropbox.com/s/di0gf37cgdwie4r/test1.pbix?dl=0
Regards,
BK
Hi @Anonymous ,
I've tried my best regarding this, the relationships and fields in it are too complicated for me.I used the following measure and it worked out a value, but the other lines always returned null, I checked for a long time and couldn't find out where the reason was.I am very sorry.We hope the following measures will provide you with inspiration.
ActlPlan_Rev_Final2 (format) =
VAR formatActl =
IF (
ISBLANK ( _Measures[ActlPlan_Rev_Range] ),
"-",
_Measures[ActlPlan_Rev_Range]
)
VAR selectedWk =
AVERAGE ( _ParaPeriod[Reporting Week (YYYYWW)] )
VAR selectedMth =
SELECTEDVALUE ( _ParaPeriod[Fiscal Month Index] )
VAR week =
IF (
selectedWk >= _Measures[Min_WkInSelectedQtr (ActlPlan)]
&& selectedWk <= [Max_WkInSelectedQtr (ActlPlan)],
formatActl,
BLANK ()
)
VAR month =
IF (
selectedMth >= _Measures[Min_MthInSelectedQtr (ActlPlan)]
&& selectedMth <= [Max_MthInSelectedQtr (ActlPlan)],
formatActl,
BLANK ()
)
VAR category =
SELECTEDVALUE ( _ParaPeriod[Category] )
VAR target =
IF ( ISBLANK ( [Target] ) || [Target] = 0, "N.A.", [Target] )
VAR prem =
FORMAT ( IF ( ISBLANK ( [Incentive_Prem %] ), 0, [Incentive_Prem %] ), "0.0%" )
VAR nonprem =
FORMAT (
IF ( ISBLANK ( [Incentive_NonPrem %] ), 0, [Incentive_NonPrem %] ),
"0.0%"
)
VAR testqqq =
CALCULATE (
IF (
ISBLANK ( [Incentive_Prem %] ),
[Incentive_Prem %] * 100,
[Incentive_Prem %] * 100
),
FILTER ( ALL ( 'Dim_Tier' ), Dim_Tier[Index] = 2 )
)
VAR testrrr =
CALCULATE (
IF (
ISBLANK ( [Incentive_NonPrem %] ),
[Incentive_NonPrem %] * 100,
[Incentive_NonPrem %] * 100
),
FILTER ( 'Dim_Tier', Dim_Tier[Index] = 2 )
)
VAR test1 =
SWITCH (
SELECTEDVALUE ( _Label[Label] ),
"Target", target,
"Premium", prem,
"Non-Premium", nonprem,
"Revenue", [Curr_PlannedRev_ActlPlan],
"Achievement", [Curr_PlannedTierAch_ActlPlan]
)
VAR test2 =
IF (
ISFILTERED ( _ParaPeriod[Para Period] ),
SWITCH ( category, "Week", week, "Month", month, "Total", formatActl ),
_Measures[ActlPlan_Rev_Range]
)
VAR test33 =
SWITCH (
SELECTEDVALUE ( Fact_Actual[Sub-Cat] ),
"Premium", test2 * testqqq,
"Non-Premium", test2 * testrrr,
TEST2
)
VAR test34 =
IF ( TEST2 <> "-", test33, "-" )
RETURN
test34
Best Regards
Lucien
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |