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
powpow11
New Member

Sum of sales for earliest date per category

Hi everyone, I have been struggling with a calcualtion for a while now, maybe someone can help.

 

I have my data as follows:

 

CategorySubcategoryCustomerDateValue
  Category1      Subcategory1      Customer1    2001-01-19     9
  Category2    Subcategory3    Customer3  2001-04-20  7
  Category3    Subcategory2    Customer2  2001-06-29  11
  Category3    Subcategory3    Customer1  2001-09-23  13
  Category1    Subcategory3    Customer1  2001-10-261
  Category2    Subcategory3    Customer3  2001-12-038
  Category1    Subcategory3    Customer2  2001-12-265
  Category1    Subcategory2    Customer2  2002-03-198
  Category3    Subcategory2    Customer2  2002-05-1313
  Category2    Subcategory3    Customer2  2002-07-201
  Category2    Subcategory2    Customer2  2002-08-1612
  Category3    Subcategory1    Customer1  2002-08-181
  Category1    Subcategory2    Customer1  2002-09-283
  Category2    Subcategory3    Customer1  2002-12-2611
  Category1    Subcategory3    Customer2  2003-03-172
  Category2    Subcategory1    Customer3  2003-06-254
  Category1    Subcategory3    Customer3  2003-07-236
  Category2    Subcategory1    Customer3  2003-09-2110
  Category2    Subcategory1    Customer1  2003-10-231
  Category2    Subcategory3    Customer2  2004-01-304

 

I am trying to build a measure to summarize sales from the earliest available data for each Category/Subcategory/Customer respectively.

The measure ValuFromEarliest = CALCULATE( sum ( data[Value] ) , filter ( data , data[Date] = min ( data[Date] ) ) )
gets the individual values right, but not the subtotals, as it can be seen below. How can I calculate a measure that works for the subtotals as well?
 

Annotation 2020-07-21 192858.jpg

 

 

Thanks

3 REPLIES 3
Greg_Deckler
Super User
Super User

@powpow11 Looks like you want a different calculation depending on your level in the hierarchy. That's what MM3TR&R was designed to achieve: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @amitchandak & @Greg_Deckler , thanks for the responses.

I coulnd't find a way to get this done with FIRSTNONBLANKVALUE, but researching this further.

 

As to solving the problem with a dynamically different calculation, it sure is feasible. While I am still hoping to find a way to somehow solve this with a less hardcoded approach, without running a different calc at different levels of the hierarchy, this is definitely a way to get this done.

 

amitchandak
Super User
Super User

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.