Forum Discussion
'What If' Commission Help
@dedelman_clng was such a huge help on my last issue but now I'm having issues with the next step of my project.
I'm trying to create a Report that will show what will happen when we change the Minimum Sales Threshold and/or the three different Market Segment Percentage Rates in What If Sliders.
The MarketSegment and Rate Variable are grabbing the Market Segment Percentage from the What If tables.
The SalesTreshold is grabbing from the Sales Threshold Whatif Table.
The SalesRate is doing all the calculations on rows where the SalesThreshold is met.
I'm trying to return all the SalesRate * MarketSegment Rate.
Whatif Commisionabe GM2 =
VAR MarketSegment =
LASTNONBLANK (
'Commission Data'[Market Segment Filter],
[Market Segment Filter]
)
VAR Rate =
(
SWITCH (
MarketSegment,
"CONTRACTS", MAXX (
VALUES ( 'Commission Rate Contract'[Contract Commission Rate] ),
[Contract Commission Rate]
),
"DISTRIBUTOR", MAXX (
VALUES ( 'Commission Rate Distributor'[Distributor Commission Rate] ),
[Distributor Commission Rate]
),
"WHOLESALE", MAXX ( VALUES ( 'Commission Rate Wholesale'[Wholesale] ), [Wholesale] )
)
)
VAR Threshold =
SELECTEDVALUE ( 'Sales Threshold'[Sales Threshold] )
VAR SalesRate =
CALCULATE (
SUMX (
FILTER ( 'Commission Data', 'Commission Data'[Sales + Discount] > Threshold ),
( 'Commission Data'[Sales + Discount] + 'Commission Data'[Originalcredits]
+ 'Commission Data'[CreditsPriorPeriod]
+ 'Commission Data'[CreditsNotAssociated]
+ 'Commission Data'[Cost]
+ 'Commission Data'[CreditCostPriorPeriod]
+ 'Commission Data'[CreditCostNotAssociated]
- 'Commission Data'[Commissionable Rebate $] )
)
)
RETURN
IF (
COUNTROWS (
FILTER ( 'Commission Data', 'Commission Data'[Sales + Discount] > Threshold )
)
= 0,
0,
( SalesRate * Rate )
)So everything works pretty well by row, but it's not aggregating correctly in my table. The table sums up to 5117.41 but the number should be 4435.09!
Any help on what I've done wrong would be much appreciated!
R
5 Replies
- Greg_DecklerCommunity Champion
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907- RAdamsHelper III
Thanks for your reply Greg_Deckler.
Turning off the Total Row isn't much of an option so I'm going to try the HasOneFilter function. Where do you recommend that I put it?
R
- Greg_DecklerCommunity Champion
Did you look at the Quick Measure? That should be your guide for how to accomplish what you are trying to do.