Calculate percentage using previous row’s data with multiple filters

[geminirand]

I'm stumped on how to handle this scenario.

I have years of book sales data. "Sell Through" is how many people who bought book 1 in a series bought book 2, and so on. I have the numbers but not as percentages that are based on another row's data.

I need to filter, as there are multiple book series in the source table, each with a Book_Title, Series_Title, and Book_Number (i.e., it's book 1 or 2, etc. in that series), and other data. Every sale has a Royalty_Date, where what matters is that month/year (not the day).

I'm trying to do a matrix that shows book title on the left as rows (Book 1, then Book 2), and the columns are the months of a year (year selected with a slicer), and the cell data is a "Sell Through" calculation.

I have an Excel sheet (first image) that shows what I want and I'm trying to duplicate the functionality in Power BI, now that the data is in a MySQL DB. In this screen shot, you can see the Sell-Through row toward the bottom. I don't really need all of these other rows as I have another report showing things like how many of each format were sold.

Sell Through is the sum of how many eBooks, paperbacks, and hard cover formats were bought for book 2, divided by the same sum for book 1. This would be needed again for the sum of book 3 divided by book 2. And so on.

Read Through is the nearly the same but without needing to sum multiple formats. I.e., the "KOLL" value of book 2 divided by the KOLL value of book 1. KOLL = a kindle unlimited subscription plan. This is different because some say that a "good" Read Through rate is not the same as a good Sell Through rate, so seeing both is good.

I'm hoping to filter the report using slicers, but if I have to "hard code" a report to show a given book series, that's fine, as I can duplicate the report for a different series. 

The relevant fields from the lone table are:

Royalty_Date - allows a grid of the months in a year and the year total, though the total (even across years) is really what matters. The goal is - "how many people stick with this book series over time?"

Books_Read_Rounded (Int) - used for Read Through (this was KOLL on the Excel sheet)

??? - I think I need a calculated field for each format (ebook, hard cover, paperback), summed, to do Total_Sales that month (to not include Books_Read_Rounded, which is KOLL)

Book_Title. 

Series_Abbr - allows me to show only a given series, as it's all about ithin a series

A mockup up Excel for the intended result:

Note on that image: I have KU Percentage on another report, so you can ignore that here (don't need it). All formats summed is not something I need to see so much as something I think is needed to calculate Read Through

I'm not sure how to break this down or do it.

For Read Through, I tried to do something like this for a measure and calculated column, but got the error "A table of multiple values was supplied where a single value was expected" -

Read Through =

VAR NextBookNumber = 'sales_data_combined'[Book_Number] + 1

    RETURN

        'sales_data_combined'[Books_Read_Rounded]

            - CALCULATE(

                VALUES('sales_data_combined'[Books_Read_Rounded]),

                FILTER(

                    ALL('sales_data_combined'), 'sales_data_combined'[Book_Number] = NextBookNumber),

                FILTER(

                    ALL('sales_data_combined'), 'sales_data_combined'[Series_Abbr] = "DGS")

            )

I'd appreciate any help. Thanks!

[geminirand]

Thank you again for the excellent help! I think I'm beginning to understand how this works.

That said, another apology - I needed two calculations and slightly confused them so that you've accurately answered my question only to have me realize I now have pieces of each. 🙂 But I have used your reply to create one of them so far, though there are two minor things not working. I'll address this one first...

"Read Through" is actually Current_Book_KOLL / Previous_Book_KOLL. (The sum work is needed for the other calculation, so ignoring that for now). Here is what I created based on your help... "Books_Read_Rounded" is effectively the same as Total_Units as far as logic

Current Book Reads =

VAR _Month = MONTH(MAX('sales_data_combined'[Royalty_Date])) -- get current month

VAR _Book = Max('sales_data_combined'[Title])  -- get the book title

VAR _CurrentBookReads =

    CALCULATE(

        SUM('sales_data_combined'[Books_Read_Rounded]),

        ALL ('sales_data_combined'),  

        MONTH('sales_data_combined'[Royalty_Date]) = _Month && 

        'sales_data_combined'[Book_Number] = MAX('sales_data_combined'[Book_Number])

        && ('sales_data_combined'[Format_Abbr] = "Kindle")

          )

RETURN

    _CurrentBookReads

Then another measure...

Read Through =

VAR _Month = MONTH(MAX('sales_data_combined'[Royalty_Date]))

VAR _Book = Max('sales_data_combined'[Title]) 

VAR _CurrentBookReads = [Current Book Reads]

VAR _PreviousBookReads =

    CALCULATE(

        SUM('sales_data_combined'[Books_Read_Rounded]),

        ALL ('sales_data_combined'),  

        MONTH('sales_data_combined'[Royalty_Date]) = _Month &&

        'sales_data_combined'[Book_Number] = MAX('sales_data_combined'[Book_Number]) - 1

        && ('sales_data_combined'[Format_Abbr] = "Kindle") 

          )

VAR _Output =

    IF(

        Max('sales_data_combined'[Book_Number]) = 1, 1, _CurrentBookReads / _PreviousBookReads

    )

    RETURN

    _Output

For the matrix, I have this. Note that I renamed the measures to try fitting this on the page. "Count" is the first measure and "RT" is the "Read Through" one.

 

This seems to be working (???), but one problem is that my Year slider doesn't appear to affect the matrix.

The other issue is that the total colum is obviously wrong for the Count and I presume for RT as well:

 

Do you know the solution?

Thanks again for the excellent help!

[v-junyant-msft · Accepted solution]

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
_Units

Previous = 
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.

[geminirand]

If it helps, this matrix shows close to what I want. Note that I removed the first column from the screenshot but each row would essentially be Book 1, Book 2, Book 3.  For January, where it says 12, I would want that to essentially say the result of 12 divided by 20 and shown as a percentage, so 60%. The row below that would show 50% (i.e., the value of 6 divided by 12).

[v-junyant-msft]

Hi @geminirand ,

I created my sample data using this screenshot:

Here is my sample data:

First, use this DAX to create a calculated column to extract the number after Book, because I need to use numbers to identify the order of Book.

Book_Number = VALUE(RIGHT('Table'[Book Title], 1))

Then use this DAX to create a measure:

Percentage = 
VAR _Format = MAX('Table'[Format])
VAR _Month = MAX('Table'[Month])
VAR _Current = 
CALCULATE(
    SUM('Table'[Value]),
    ALL('Table'),
    'Table'[Book_Number] = MAX('Table'[Book_Number]) - 1 && 'Table'[Format] = _Format && 'Table'[Month] = _Month
)
VAR _Output = 
IF(
    MAX('Table'[Book_Number]) = 1,
    1,
    SUM('Table'[Value]) / _Current
)
RETURN
_Output

Create the matrix as shown in the screenshot and the final output is as below:

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.

[geminirand]

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:

Non-Kindle Formats = CALCULATE(

    SUM ('sales_data_combined'[Total_Units]),

    ALL('sales_data_combined'),

     'sales_data_combined'[Format_Abbr] = "PPB" || 'sales_data_combined'[Format_Abbr] = "HC" || 'sales_data_combined'[Format_Abbr] = "eBook" || 'sales_data_combined'[Format_Abbr] = "Audio")

This measure is now one field in my model, and your measure is another field. How can I have them be the only two rows under Book 1, Book 2, etc.? It can't be two separate measures, can it?

Thanks so much!

[v-junyant-msft]

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
_Units

all 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
_Output

But 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.

[geminirand]

My measure is wrong in some way. It always shows 42407 for all results

[geminirand]

I think one reason for this is that I need to sum by the series slider. The report has sliders for series, year, and author name