Forum Discussion

Anonymous

Not applicable

6 years ago

Reverse Running Total with Blank Columns

My last post was reported for some reason. PLEASE DON'T REPORT AS SPAM!

Got a weird one for you guys. I am building a reverse running total matrix that has the week in the quarter across the columns. The rows show the different year/quarter combinations. The measure is meant to reflect the revenue that was generated on or after the current week based on the column. So, for example, there was a total of $2,486,602 in revenue for 2019 Q1, as shown in the table below. In week 1, the entire revenue for the quarter is shown, because all revenue was generated on or after week 1 in the quarter. This amount should logically decrease for each week throughout the quarter. If no revenue was generated in a specific week, that column should reflect the same amount as the column before it...

That's where I'm running into an issue. When a given week does NOT have any revenue data, the matrix cell is NOT showing the same value as the prior column. Instead, it is showing the the full revenue for the quarter (equal to the value in the week 1 column).

See the yellow highlighted cells below for an example. For 2019 Q1, week 13 reflects the same value as week 1. I have no idea why the same didn't happen for 2019 Q4...

Here is another example, this time with 2019 Q1 split by region. You can see that the same thing is happening for any week where there is no revenue data for the week/year/quarter/region combination.

I need those yellow cells to be equal to the prior column. Here's what I've already tried:

I've tried adding "+ 0" to the end of the measure. Doesn't work.
I've tested adding $0 lines to the data table this pulls from. That does work, however it is not an ideal solution for a host of reasons. I don't want dummy data in the model if I can help it.

I'm fairly certain this should be doable by adjusting the measure formula, but all of the forum posts I've seen regarding this topic discuss how to fix the blank column issues for running totals and not for reverse running totals.

Thanks so much for your help! Here is my measure formula for your reference:

Revenue On or After =

CALCULATE(

SUM('Revenue Detail Table'[Revenue]),

FILTER(

ALLSELECTED('Revenue Detail Table'[Week]),

'Revenue Detail Table'[Week] >= MIN('Revenue Detail Table'[Week])

)

Need Help

11 Replies

Anonymous
Not applicable
6 years ago

Got a weird one for you guys. I am building a reverse running total matrix that has the week in the quarter across the columns. The rows show the different year/quarter combinations. The measure is meant to reflect the revenue that was generated on or after the current week based on the column. So, for example, there was a total of $2,486,602 in revenue for 2019 Q1, as shown in the table below. In week 1, the entire revenue for the quarter is shown, because all revenue was generated on or after week 1 in the quarter. This amount should logically decrease for each week throughout the quarter. If no revenue was generated in a specific week, that column should reflect the same amount as the column before it...

That's where I'm running into an issue. When a given week does NOT have any revenue data, the matrix cell is NOT showing the same value as the prior column. Instead, it is showing the the full revenue for the quarter (equal to the value in the week 1 column).

See the yellow highlighted cells below for an example. For 2019 Q1, week 13 reflects the same value as week 1. I have no idea why the same didn't happen for 2019 Q4...
No Slicer

Here is another example, this time with 2019 Q1 split by region. You can see that the same thing is happening for any week where there is no revenue data for the week/year/quarter/region combination.
Region Slicer

I need those yellow cells to be equal to the prior column. Here's what I've already tried:
I've tried adding "+ 0" to the end of the measure. Doesn't work.
I've tested adding $0 lines to the data table this pulls from. That does work, however it is not an ideal solution for a host of reasons. I don't want dummy data in the model if I can help it.

I'm fairly certain this should be doable by adjusting the measure formula, but all of the forum posts I've seen regarding this topic discuss how to fix the blank column issues for running totals and not for reverse running totals.

Thanks so much for your help! Here is my measure formula for your reference:

Revenue On or After =
CALCULATE(
    SUM('Revenue Detail Table'[Revenue]),
    FILTER(
        ALLSELECTED('Revenue Detail Table'[Week]),
        'Revenue Detail Table'[Week] >= MIN('Revenue Detail Table'[Week])
    )
)
- VasTg
  Memorable Member
  6 years ago
  Anonymous
  
  Whats the create week column? and where does the column value (1 to 13) in the screenshot comes from?
  
  Looking at the DAX, "MIN('Revenue Detail Table'[Create Week])" is returning 1 or 0 for the weeks you don't have an entry in the table.
  
  Post some sample data with all the used columns to better assist you.
  - Anonymous
    Not applicable
    6 years ago
    Oops. That should read "Week" not "Create Week." I've updated the post. All referenced columns are in the 'Revenue Detail Table' data table.
    
    You are correct that the DAX appears to be returning 1 or 0 in the case of missing data. How can I get that to return the number at the head of the relevant column?
    
    Thank you,
    David
VasTg
Memorable Member
6 years ago
Anonymous

i did not.. I was responding to you..
- Anonymous
  Not applicable
  6 years ago
  I didn't mean to accuse you! I was just tagging you for visibility to this new post since the other one was deleted. Thanks for your responses.
Greg_Deckler
Community Champion
6 years ago
I love solving the weird ones. But I also hate typing. Can you post sample data please? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
- smpa01
  Community Champion
  6 years ago
  Greg_Deckler I am going to start using this from now on ...😀😀 You are just not only a legend in solving power bi problems🙏
  
  "I love solving the weird ones. But I also hate typing"
- Anonymous
  Not applicable
  6 years ago
  
  Greg_Deckler
  
  Thanks for the tip, Greg! I've scrubbed my Power BI Desktop file so it can be shared, but I'm not sure how to attach it to this post. Any ideas there? Is there some other way I should post sample data?
  
  Thanks!
  - Greg_Deckler
    Community Champion
    6 years ago
    Most folks just post it to OneDrive, Box, etc. and share a link.

Forum Discussion

Reverse Running Total with Blank Columns

11 Replies

Related content

Reverse Running Total based On Another Column

Reverse Running Total Measure per Category/Bin (WINDOW Function) - Avoiding Multiple Tables

Running Maximum

Running sum

Formatted Table Reversing Causes Errors