Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am getting incorrect column sub totals in matrix. Tried with a simple table as well, still the totals are incorrect.
Have an employee master, store master, product master, target master and sales transaction table.
Each product belongs to a category. Each employee belongs to a store and category.
Example: Employee John belongs to category Mobile in Store XYZ.
If a target of a category is achieved, all employees within that category should receive an incentive and this is to be doubled if 2nd day also the target is met. I am able to get the values, however in doing so, the column sub totals in the matrix then shows incorrect totals.
CDay = Current Day
PDay = Previous Day
The formula also checks if its 1st of the month. If so, then double incentive does not apply on 1st.
Below is my DAX formula:
Salespersonincentiveactual =
var CDay=CALCULATE(SUM('Daily Sales Transaction by employee'[SalespersonIncentive]))
var PDDay= CALCULATE(SUM('Daily Sales Transaction by employee'[SalespersonIncentive]),
DATEADD('Calendar'[Date],-1,DAY))
var Final=
If(MAX('Calendar'[Day]) = 1, CDay,
IF
(
PDDay>0 && CDay>0,CDay*2,CDay
)
)
RETURN IF(Final=0,BLANK(),Final)
Tried summarize as well, still didnt work -
SalespersonIncentiveActual =
SUMX(
SUMMARIZE('Calendar', 'Calendar'[Date]),
VAR CDay = CALCULATE(SUM('Daily Sales Transaction by employee'[SalespersonIncentive]))
VAR PDDay = CALCULATE(SUM('Daily Sales Transaction by employee'[SalespersonIncentive]), DATEADD('Calendar'[Date], -1, DAY))
VAR Final = IF(MAX('Calendar'[Day]) = 1, CDay, IF(PDDay > 0 && CDay > 0, CDay * 2, CDay))
RETURN IF(Final = 0, BLANK(), Final)
)
Kindly advise.
@amitchandak @Greg_Deckler @parry2k
@PowerDigitize_1 I will defer it to @Greg_Deckler since he is the master and running a big campaign for scenarios like this.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.