Forum Discussion

RichHead1821's avatar
RichHead1821
Icon for Resolver I rankResolver I
5 years ago
Solved

Measure 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[Targe...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    RichHead1821 

    Try:

    netTargetWeek = 
    CALCULATE ( SUM ( dimTargets[Target] ),
    ALLEXCEPT ( DimTarget, Dates [uniqueWeek] )))
    (Assuming there is a relationship between DimTarget and your dates table)
RichHead1821's avatar
RichHead1821
Icon for Resolver I rankResolver I
5 years ago

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, thanks

 

NP2020's avatar
NP2020
Frequent Visitor
5 years ago

Many thanks for this - I have been able to use this also for a similar scenario!