Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone, I have been struggling with a calcualtion for a while now, maybe someone can help.
I have my data as follows:
Category | Subcategory | Customer | Date | Value |
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-26 | 1 |
Category2 | Subcategory3 | Customer3 | 2001-12-03 | 8 |
Category1 | Subcategory3 | Customer2 | 2001-12-26 | 5 |
Category1 | Subcategory2 | Customer2 | 2002-03-19 | 8 |
Category3 | Subcategory2 | Customer2 | 2002-05-13 | 13 |
Category2 | Subcategory3 | Customer2 | 2002-07-20 | 1 |
Category2 | Subcategory2 | Customer2 | 2002-08-16 | 12 |
Category3 | Subcategory1 | Customer1 | 2002-08-18 | 1 |
Category1 | Subcategory2 | Customer1 | 2002-09-28 | 3 |
Category2 | Subcategory3 | Customer1 | 2002-12-26 | 11 |
Category1 | Subcategory3 | Customer2 | 2003-03-17 | 2 |
Category2 | Subcategory1 | Customer3 | 2003-06-25 | 4 |
Category1 | Subcategory3 | Customer3 | 2003-07-23 | 6 |
Category2 | Subcategory1 | Customer3 | 2003-09-21 | 10 |
Category2 | Subcategory1 | Customer1 | 2003-10-23 | 1 |
Category2 | Subcategory3 | Customer2 | 2004-01-30 | 4 |
I am trying to build a measure to summarize sales from the earliest available data for each Category/Subcategory/Customer respectively.
Thanks
@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-...
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.
@powpow11 , Firstnonblankvalue can help. Refer usage in my blog
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |