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.
I have two measures related to two distinct tables, so SALESACCUMULATIVE (running total of sales) to SALES and TARGETACCUMULATIVE (running total of target) to TARGET. I want give two points to the salesman if SALESACCUMULATIVE > TARGETACCUMULATIVE by month so this is what the table would look like
DATE - SALESACCUMULATIVE - TARGETACCUMULATIVE - POINTS
JAN 100 100 2
FEB 150 200 0
MAR 350 300 2
TOTAL 4
Hi @moizsherwani,
So are you after the calcuation for your Points column?
If so it could be this :
points = if([SALESACCUMULATIVE] > [TARGETACCUMULATIVE] , 2 , 0 )
It does not work when I do that because these are running totals. What happens is that by the end it is comparing the total running target with the total sales whereas it should be adding the sum of each month
MONTH TARGET SALE POINTS
JAN 2 3 2
FEB 4 4 0
MAR 6 7 2
ENDS UP AS
TOTAL 6 7 2
SHOULD BE 4
Hi @moizsherwani,
What is the formula you are using for the cumulative values? You can probably use something like this for your POINTS column
@Phil_Seamark also on the cumulative measure I am summing a column whereas here I am trying to sum a measure :(. any ideas?
Hi @moizsherwani,
Sorry for the delayed response. It's hard for me to tell where you have used pre-calculated data and where you are using measures and this makes a difference in the measure I would suggest.
Any chance you can strip out your PBIX file down to the bare minimum and share it with me so I can have a look.
Feel free to send the link via private message, but I will repond with any reply here 🙂
@Phil_Seamark, sorry for the delayed response from my side this time. I may not be able to share the file at any level due to security reasons but the way I resolved this was using a supportive calculated column.
In my Target table I created a calculated column that would cumulatively sum the sales for each month from the SALES table and display them next to each month target
MonthSales = CALCULATE(
SUM(SALES[Amount]),
FILTER(
SALES,
SALES[OrderDate]<='TARGET'[TargetEndDate]&&
YEAR(SALES[OrderDate])=YEAR('TARGET'[TargetEndDate])
)
)
I then added a measure to my target table
Points = CALCULATE(
COUNT('TARGET'[TARGETId]),
FILTER('TARGET',
'TARGET'[MonthSales]>'TARGET'[TargetAmount]
)
) * 2
This does the work for me, if you notice I multiplied the count by 2 which gives me the points, so the logic was that count all the target rows where the sales > goal. So the reason I shared this with you was to also get your thoughts on the solution and a better one if it exists, I have heard it is not good practice to add calculated columns so I try my best not to do so but this time I could not figure out another way.
Hi @moizsherwani,
Well done and it looks good. Calculated columns have their place and can be very useful, particularly if you want to lock a value to the row. You'll just have to decide what works best for you in this case.
Its easy enough to convert across to a Measure if you need,.
I am using something along the lines of
GoalTarget = CALCULATE(
SUM('Goal'[TargetMoney]),
FILTER('Goal',
'Goal'[GoalStartDate]<LASTDATE(CommonDate[Date])+1&&
year('Goal'[GoalStartDate])=YEAR(LASTDATE(CommonDate[Date])))
)
So I tried this as well but I am not sure what to replace the SUM('Goal'[TargetMoney]) with? Something along the lines of For Each month where SalesAccumulative>TargetAccumulative, x= x +2.
I use something like
GOALACCUMULATIVE = CALCULATE(
SUM(GOALAMOUNT),
FILTER(GOAL
GOALSTARTDATE<LASTDATE(CommonDate[Date])&&
year(GOALSTARTDATE)=YEAR(LASTDATE(CommonDate[Date])))
)
I tried something similar but I don't know what to do with the SUM(GOALAMOUNT) in the above formula <-- how do I tell it to keep adding a 2 for every SALES>GOAL
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |