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.

  • geminirand's avatar
    geminirand
    Frequent Visitor
    2 years ago

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

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

      • geminirand's avatar
        geminirand
        Frequent Visitor
        2 years ago

        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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.