Forum Discussion

geminirand's avatar
geminirand
Frequent Visitor
2 years ago
Solved

Calculate percentage using previous row’s data with multiple filters

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 a...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
geminirand
Frequent Visitor
2 years ago

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!
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.