Forum Discussion
Calculate percentage using previous row’s data with multiple filters
Hi geminirand ,
Use this two DAXs to create measures:summed = VAR _Month = MONTH(MAX('Table'[Royalty_Date])) VAR _Book = MAX('Table'[Title]) VAR _Units = CALCULATE( SUM('Table'[Total_Units]), ALLEXCEPT('Table', 'Table'[Royalty_Date].[Year]), MONTH('Table'[Royalty_Date]) = _Month && 'Table'[Title] = _Book && ('Table'[Format_Abbr] = "Audio" || 'Table'[Format_Abbr] = "eBook" || 'Table'[Format_Abbr] = "HC" || 'Table'[Format_Abbr] = "PPB" ) ) RETURN _UnitsPrevious = VAR _Month = MONTH(MAX('Table'[Royalty_Date])) VAR _Book = MAX('Table'[Title]) VAR _Previous = CALCULATE( SUM('Table'[Total_Units]), ALLEXCEPT('Table', 'Table'[Royalty_Date].[Year]), MONTH('Table'[Royalty_Date]) = _Month && 'Table'[Book_Number] = MAX('Table'[Book_Number]) - 1 && ('Table'[Format_Abbr] = "Audio" || 'Table'[Format_Abbr] = "eBook" || 'Table'[Format_Abbr] = "HC" || 'Table'[Format_Abbr] = "PPB") ) RETURN _Previous
Especially here:
Then create two more measures to refer to the above two measures:all formats summed = SUMX(VALUES('Table'[Royalty_Date].[Month]), [summed])all formats percentage = VAR _a = SUMX(VALUES('Table'[Royalty_Date].[Month]), [all formats summed]) VAR _b = SUMX(VALUES('Table'[Royalty_Date].[Month]), [Previous]) RETURN IF( _b = BLANK(), 1, _a / _b )Then put these two measures above into the Matrix's Values:
And the final output is as below:
47+35+34=116
42+29+36=107
(42+29+36) / (47+35+34)=92.24%And when I change the slicer:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks so much for this. It is very helpful.
My sample data doesn't quite look like yours but is close. I currently don't have "all formats summed" plus a row for KOLL (aka Kindle). I thought this would be easy and didn't include it in my question, so that's my fault.
This part is not a response to your response, just explanation.... If I use the Sum of Total_Units in the Matrix, like this...
It looks like this - note that this screenshot is not showing the column headers, and above "Audio" is "Book 1" and below "PPB" would be Book 2. This is the expanded area under Book 1. Sorry if this is confusing but I'm trying to hide book titles for anonymity
Is there a way to sum Audio, ebook, HC, and PPB (everything but Kindle/aka KOLL) to "all formats summed" and have another line be the existing Kindle data as calculated by your measure? That's the missing part. I got everything you wrote to work but I need the "all formats summed" row.
I tried this and it seems to work as a measure but maybe it's wrong:
Hi geminirand ,
Based on your response, I have updated my sample data (On Page2 of the pbix):
TitleFormat_AbbrRoyalty_DateTotal_Units
| Book 1 | Audio | 2023.1.31 | 1 |
| Book 1 | eBook | 2023.1.31 | 20 |
| Book 1 | HC | 2023.1.31 | |
| Book 1 | Kindle | 2023.1.31 | 20 |
| Book 1 | PPB | 2023.1.31 | 26 |
| Book 1 | Audio | 2023.2.28 | 2 |
| Book 1 | eBook | 2023.2.28 | 4 |
| Book 1 | HC | 2023.2.28 | 3 |
| Book 1 | Kindle | 2023.2.28 | 10 |
| Book 1 | PPB | 2023.2.28 | 26 |
| Book 1 | Audio | 2023.3.31 | 3 |
| Book 1 | eBook | 2023.3.31 | 11 |
| Book 1 | HC | 2023.3.31 | |
| Book 1 | Kindle | 2023.3.31 | 6 |
| Book 1 | PPB | 2023.3.31 | 20 |
| Book 2 | Audio | 2023.1.31 | 4 |
| Book 2 | eBook | 2023.1.31 | 18 |
| Book 2 | HC | 2023.1.31 | |
| Book 2 | Kindle | 2023.1.31 | 22 |
| Book 2 | PPB | 2023.1.31 | 20 |
| Book 2 | Audio | 2023.2.28 | 1 |
| Book 2 | eBook | 2023.2.28 | 2 |
| Book 2 | HC | 2023.2.28 | 6 |
| Book 2 | Kindle | 2023.2.28 | 15 |
| Book 2 | PPB | 2023.2.28 | 20 |
| Book 2 | Audio | 2023.3.31 | 1 |
| Book 2 | eBook | 2023.3.31 | 10 |
| Book 2 | HC | 2023.3.31 | |
| Book 2 | Kindle | 2023.3.31 | 9 |
| Book 2 | PPB | 2023.3.31 | 25 |
| Book 3 | Audio | 2023.1.31 | 5 |
| Book 3 | eBook | 2023.1.31 | 22 |
| Book 3 | HC | 2023.1.31 | |
| Book 3 | Kindle | 2023.1.31 | 28 |
| Book 3 | PPB | 2023.1.31 | 26 |
| Book 3 | Audio | 2023.2.28 | 2 |
| Book 3 | eBook | 2023.2.28 | 2 |
| Book 3 | HC | 2023.2.28 | 8 |
| Book 3 | Kindle | 2023.2.28 | 18 |
| Book 3 | PPB | 2023.2.28 | 24 |
| Book 3 | Audio | 2023.3.31 | 2 |
| Book 3 | eBook | 2023.3.31 | 20 |
| Book 3 | HC | 2023.3.31 | |
| Book 3 | Kindle | 2023.3.31 | 13 |
| Book 3 | PPB | 2023.3.31 | 30 |
As before, we need such a calculated column:
Book_Number = VALUE(RIGHT('Table (2)'[Title], 1))
Then use these DAXs to create two measures:
all formats summed =
VAR _Month = MONTH(MAX('Table (2)'[Royalty_Date]))
VAR _Book = MAX('Table (2)'[Title])
VAR _Units =
CALCULATE(
SUM('Table (2)'[Total_Units]),
ALL('Table (2)'),
MONTH('Table (2)'[Royalty_Date]) = _Month && 'Table (2)'[Title] = _Book && ('Table (2)'[Format_Abbr] = "Audio" || 'Table (2)'[Format_Abbr] = "eBook" || 'Table (2)'[Format_Abbr] = "HC" || 'Table (2)'[Format_Abbr] = "PPB"
)
)
RETURN
_Unitsall formats percentage =
VAR _Month = MONTH(MAX('Table (2)'[Royalty_Date]))
VAR _Book = MAX('Table (2)'[Title])
VAR _Current = [all formats summed]
VAR _Previous =
CALCULATE(
SUM('Table (2)'[Total_Units]),
ALL('Table (2)'),
MONTH('Table (2)'[Royalty_Date]) = _Month && 'Table (2)'[Book_Number] = MAX('Table (2)'[Book_Number]) - 1 && ('Table (2)'[Format_Abbr] = "Audio" || 'Table (2)'[Format_Abbr] = "eBook" || 'Table (2)'[Format_Abbr] = "HC" || 'Table (2)'[Format_Abbr] = "PPB")
)
VAR _Output =
IF(
MAX('Table (2)'[Book_Number]) = 1,
1,
_Current / _Previous
)
RETURN
_OutputBut you mentioned ‘How can I have them be the only two rows under Book 1, Book 2’, this can not be achieved, because the measures can not be placed on the rows, so in the matrix, the two measures can only be in two columns instead of two rows:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.