Forum Discussion
RichHead1821
Resolver I
Resolver IMeasure to create a summed value based upon matching values in another column?
I want to create a weekly target from daily targets, so have been trying to group on "uniqueWeek" (a column holding YEAR&WEEK as a string), i.e.:
netTargetWeek = SUMX(
VALUES ( dimTargets[Target] ),
CALCULATE ( MAX ( dimTargets[Target] ),
GROUPBY ( Dates, [uniqueWeek] )))
dimTargets has a date which is joined to the Dates table.
I get the same value in my measure as in the daily targets [Target]
ID | Target | Date
1 £10 04/01/2021
2 £10 05/01/2021
Both dates share the same uniqueWeek (202101), my measure returns:
ID 1 = £10
ID 2 = £10
I want them both to be £20 (as they share the same uniqueWeek.
I am sure I have done this before, but can't get it....
Try:
netTargetWeek =CALCULATE ( SUM ( dimTargets[Target] ),ALLEXCEPT ( DimTarget, Dates [uniqueWeek] )))(Assuming there is a relationship between DimTarget and your dates table)
3 Replies
PaulDBrownCommunity Champion
Try:
netTargetWeek =CALCULATE ( SUM ( dimTargets[Target] ),ALLEXCEPT ( DimTarget, Dates [uniqueWeek] )))(Assuming there is a relationship between DimTarget and your dates table)- RichHead1821
Thanks Paul, there is a relationship between the target[date] and the dates[date], it changed the outcome, but not as expected (however that was my fault for not explaining there are different people in the target table, adding an additional dimension (for the person) into the ALLEXCEPT resolved that for me.
Just to understand the solution
netTargetWeek =CALCULATE ( SUM ( dimTargets[Target] ),ALLEXCEPT ( dimTarget, Dates [uniqueWeek], dimTarget[Person] )))The ALLEXCEPT removes all filters from dimTarget except for the date (uniqueWeek) and person.Nice, thanksMany thanks for this - I have been able to use this also for a similar scenario!
