Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RichHead1821
Resolver I
Resolver I

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[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....
1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@RichHead1821 

Try:

netTargetWeek = 
CALCULATE ( SUM ( dimTargets[Target] ),
ALLEXCEPT ( DimTarget, Dates [uniqueWeek] )))
(Assuming there is a relationship between DimTarget and your dates table)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
PaulDBrown
Community Champion
Community Champion

@RichHead1821 

Try:

netTargetWeek = 
CALCULATE ( SUM ( dimTargets[Target] ),
ALLEXCEPT ( DimTarget, Dates [uniqueWeek] )))
(Assuming there is a relationship between DimTarget and your dates table)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.