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

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.