locked
Displaying cumulating numbers in a pivot table when there is no value RRS feed

  • Question

  • Hi Everyone

    I have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom.

    The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:

    1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent--see the first yellow line in the screenshot below. There should be a zero displayed for both values.

    2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.

    Here is the formula for the numerator:

    =CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory])))

    Here is the formula for the percent:

    =(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))

    If anyone has any idea how to resolve this, it would be greatly appreciated!! Also, I have tried 'Repeat All Item Labels'. It does not work.

    Paul


    Wednesday, March 11, 2015 3:07 PM

Answers

  • Hi Paul,

    it is crucial that you reference your FILTER-arguments to a different table than your fact table (s1Perm1):

    Your formula above will only work, if you have a DimDate-table that contains a continuous series of dates and this would then have to be referenced like this:

    :=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(DimDate[Date]),DimDate[Date] <= MAX(DimDate[Date])))

    like in the link posted above.

    If you don't want to use a full date table, you can achieve what you want by using a table (called "Months" with months and years in a column and use a formula like this:

    :=[Summe entity_id](FILTER(ALL('Months');'Months'[Month]<=MAX(Months[Month]));VALUES(Months[Year]))

    It is crucial that you have a reference table that contains unique values that are marked as date tables or at least sorted accordingly.

    In your pivot report table you need to drag the month from these reference tables into the row section - don't use the ones from your s1Perm1-fact-table!


    Imke


    Tuesday, March 17, 2015 6:38 PM
    Answerer
  • Hi Paul,

    yes, keep the isblank-wrapping (in order to show the first missing values as 0), but replace FILTER arguments by external table. You need a unique identifier, so if you go ahead with Year/Month only, concatenate them for a key field.

    I've heard of EARLIER used in measures, but have no experience with it - only use them in calcColumns.


    Imke

    Tuesday, March 17, 2015 7:47 PM
    Answerer
  • Hi Paul,

    what are these "months"? If your cumulate over time one would expect them to be 1-12 wihtin a year.

    In the formula you need to take the unique key from your date table (according to Date[date] in the reference example).


    Imke

    Friday, March 20, 2015 5:54 AM
    Answerer
  • HI Paul,

    this one grew quite considerably compared to your first post :-)

    Try to create the logic from your first post like this:

    Percent:=DIVIDE(YourNewNumeratorMeasure, CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))

    check if it works and then apply your other conditions one by one - checking on where it exactly throws the error.


    Imke

    Monday, March 23, 2015 9:32 PM
    Answerer
  • Hi Paul,

    No, it shouldn’t.

    Try taking the 1<sup>st</sup> two columns in your report from s1perm1 instead of from the date table.


    Imke

    Thursday, March 26, 2015 12:24 PM
    Answerer
  • You're absolutely right about that - I'm pretty sure there is another way to do this.

    Just that I'm not the expert to know about it :-)


    Imke

    • Marked as answer by Paul-NYS Wednesday, July 1, 2015 4:08 PM
    Wednesday, July 1, 2015 3:25 PM
    Answerer

All replies

  • Hi Paul,

    try wrapping your 1st measure in this: IF(ISBLANK(measure),0,measure)

    If working with dates, you should always use a DIM_Date table with continuous dates to refer to. Then hide your date fields in the other tables from client tools and only use the DIM Dates fields in your reports and mesures.

    See: http://www.daxpatterns.com/cumulative-total/


    Imke


    Friday, March 13, 2015 6:21 AM
    Answerer
  • Hi Imke

    That works great for when there is no value in the "#" (numerator) column for rows 4,5,6--see below screenshot. However, per problem #2 above, these are cumulative numbers and percents from top to bottom. When there is a value in the "#" (numerator) column for a given row (row 9), but there is not a value in the "#" column for the row immediately after (row 12), I would like for the value of the prior row (row 9) to display rather than 0--because there are cumulative numbers and percents from top to bottom.

    You can see the issue on the below screenshot where the two red lines are.

    Current formula for the "#" column (incorporating your change for inserting the 0):

    =IF(ISBLANK(CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory]<=MAX(s1Perm1[ExitMonthCategory])))),0,CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))

    Screenshot (two red lines):







    • Edited by Paul-NYS Tuesday, March 17, 2015 2:38 PM
    Tuesday, March 17, 2015 2:19 PM
  • Hi Paul,

    it is crucial that you reference your FILTER-arguments to a different table than your fact table (s1Perm1):

    Your formula above will only work, if you have a DimDate-table that contains a continuous series of dates and this would then have to be referenced like this:

    :=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(DimDate[Date]),DimDate[Date] <= MAX(DimDate[Date])))

    like in the link posted above.

    If you don't want to use a full date table, you can achieve what you want by using a table (called "Months" with months and years in a column and use a formula like this:

    :=[Summe entity_id](FILTER(ALL('Months');'Months'[Month]<=MAX(Months[Month]));VALUES(Months[Year]))

    It is crucial that you have a reference table that contains unique values that are marked as date tables or at least sorted accordingly.

    In your pivot report table you need to drag the month from these reference tables into the row section - don't use the ones from your s1Perm1-fact-table!


    Imke


    Tuesday, March 17, 2015 6:38 PM
    Answerer
  • Hi Imke

    The month and year values I am using are just numbers in two separate columns. They aren't dates actually. If I separate the month and year values in a separate table from s1Perm1, the original formula will resolve both issues?

    1. When there is no value in the "#" (numerator) column for rows 4,5,6--see below screenshot. Display a zero.

    2. When there is a value in the "#" (numerator) column for a given row (row 9), but there is not a value in the "#" column for the row immediately after (row 12), I would like for the value of the prior row (row 9) to display rather than 0--because there are cumulative numbers and percents from top to bottom.

    Alternatively, using your amended formula with the isblank formula, I thought I was close to resolving the second issue above by incorporating an Earlier function into the isblank, however, I am having an issue with the earlier function. Logically, this will not work?

    =IF(ISBLANK(CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory]<=MAX(s1Perm1[ExitMonthCategory])))),

    if(earlier([Numerator-new],1)<>0,earlier([Numerator-new],1),0), CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))







    • Edited by Paul-NYS Tuesday, March 17, 2015 7:27 PM
    Tuesday, March 17, 2015 7:21 PM
  • Hi Paul,

    yes, keep the isblank-wrapping (in order to show the first missing values as 0), but replace FILTER arguments by external table. You need a unique identifier, so if you go ahead with Year/Month only, concatenate them for a key field.

    I've heard of EARLIER used in measures, but have no experience with it - only use them in calcColumns.


    Imke

    Tuesday, March 17, 2015 7:47 PM
    Answerer
  • Hi Imke

    I just want to verify the approach:

    1. Create a date dimension table: it will have Month, Year, and a MonthYear columns with MonthYear being the PK. The month and year values are just numbers (6, 12, 18, 24, 30, 36.....) for months and 2006, 2007 2008 for the year values.

    2. Add a MonthYear column in the s1Perm1 fact/data table and link to the above date table.

    3. Create a revised function, based on yours above, that incorporates the isblank function and looks something like the below that incorporates the new Date dimension table (and if you can, please take a look, I usually use one table in DAX). I am still using Filter in this?

    =IF(ISBLANK(CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(dimDate[Month]),dimDate[Month] <= MAX(dimDate[Month]))),0,CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(dimDate[Month]),dimDate[Month] <= MAX(dimDate[Month]))))

    4. Use the month column from the above Date table in the pivot table row section and NOT the month in in the  s1Perm1 data table.

    Is this correct and everything?

    Paul

    Thursday, March 19, 2015 9:00 PM
  • Hi Paul,

    what are these "months"? If your cumulate over time one would expect them to be 1-12 wihtin a year.

    In the formula you need to take the unique key from your date table (according to Date[date] in the reference example).


    Imke

    Friday, March 20, 2015 5:54 AM
    Answerer
  • Hi Imke

    There are no actual dates in any table I have. I have year values currently stored in an individual column in the s1Perm1 data table as just the year itself: 2006, 2007, 2008, etc. and I have month values stored in its own column in the s1Perm1 table: 6,12,18,24,30...

    The month values are just 6 month intervals up to about 96 months. They represent month intervals from a given entry year. We measure the time children exit from foster care. So we have their year of entry in foster care (in the Year column above) and the 6 month interval from the entry year they exited foster care. So if a children entered care in 2007, he/she could exit care 18 months after 2007. We capture both the entry year (2007) and the 6 month interval during which he/she exited (18 months in this case).

    Paul


    • Edited by Paul-NYS Friday, March 20, 2015 1:29 PM
    Friday, March 20, 2015 1:20 PM
  • Hi Paul,

    that's different to what I've done before.

    Maybe you just add an index column to your date table then, that has a continuous row of numbers. Take this as the key field in your DimDate table and use it in the formular and the other fields from this table in your Pivot Report.


    Imke

    Friday, March 20, 2015 1:34 PM
    Answerer
  • Hi Ilke

    I applied your above solution (creating a separate, but linked Time table with the ExitMonthCategory column in it along with others) and used the ExitMonthCategory from Time in my pivot table row. It seems to now work fine with this in that it addresses the above two issues: it displays the zero when there is nothing and continually displays the cumulative numerator even for rows when there is nothing to add to it.

    The problem is when I apply the same use of ExitMonthCategory in the separate Time table to the cumulative percent column. It is giving me a DAX error indicating I can only use the ALL function for columns that are in the same table. So my question is, with the below function is there an alternative to using the ALL function that will allow for referencing linked tables?

    =if(HASONEVALUE('s1JudgeIds'[JudgeName]),if(values('s1JudgeIds'[JudgeName])<>"1 - All Judges",IF(ISBLANK((CALCULATE(countrows(s1Perm1),FILTER(ALL(Time[ExitMonthCategory]),Time[ExitMonthCategory]<=MAX(Time[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],Time[ExitMonthCategory])))),0,(CALCULATE(countrows(s1Perm1),FILTER(ALL(Time[ExitMonthCategory]),Time[ExitMonthCategory] <=MAX(Time[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],Time[ExitMonthCategory])))),[CumPercent]))

    Paul

    Monday, March 23, 2015 9:03 PM
  • HI Paul,

    this one grew quite considerably compared to your first post :-)

    Try to create the logic from your first post like this:

    Percent:=DIVIDE(YourNewNumeratorMeasure, CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))

    check if it works and then apply your other conditions one by one - checking on where it exactly throws the error.


    Imke

    Monday, March 23, 2015 9:32 PM
    Answerer
  • That worked fine with one minor adjustment:

    Percent:=DIVIDE(YourNewNumeratorMeasure, CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit]),ALL(s1Perm1[ExitMonthCategory])))

    Now cumulative numerator, denominator and cumulative percent all seem to work fine, however, there is one issue: the ExitMonthCategory variable that is now in the pivot table row and is from the separate Time table is displaying all exit months for all entry cohort years. There are only exit month values (and data) for each entry cohort year relative to the time expired from 12/31 of the entry ccohort year to the run date of the data (12/31/14 in this case).

    So for example, for entry cohort year 2010, there are 3 month intervals for up to 48 months, for entry cohort year 2011, there are 3 month intervals for up  to 36 months, etc. The pivot table displays all exit month values up to 96 months for all entry years. 96 months is only appropriate for entry year 2006.

    When I used the ExitMonthCategory in the s1Perm1 data table it automatically stops for each entry year because there is no data beyond it--there is no data beyond in the Time table either. But it doesn't stop with the external Time table (below screenshot: 2010 should stop at 48 months).

    Should this still function this way or does it need to be coded around?

    Paul


    • Edited by Paul-NYS Tuesday, March 24, 2015 4:17 PM
    Tuesday, March 24, 2015 4:15 PM
  • Hi Paul,

    No, it shouldn’t.

    Try taking the 1<sup>st</sup> two columns in your report from s1perm1 instead of from the date table.


    Imke

    Thursday, March 26, 2015 12:24 PM
    Answerer
  • The year column was from s1perm1, however, when I changed the ExitMonth category back to the s1perm1, none of the variables are cumulating any longer--the same as the initial problem. Actually, if I use the ExitMonth variable from s1perm1, there doesn't seem to be a need for the Time table?

    I will keep playing around with it..

    Paul

    Friday, March 27, 2015 5:05 PM
  • Hi Paul,

    Any more progress with this or is it still unresolved?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, April 28, 2015 9:20 AM
  • Hi Imke

    I have a belated response to your above reply to my original post: I did get your solution eventually to work by using the external reference table. However, to understand DAX more and for future purposes, I was wondering if there is a way to change the original formula so that I don't have to use the external reference table? Is there something other than filter statement that can be used?

    Below is the original formula and your reply using the reference table in the filter statement.

    Original formula:

    =CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory])))

    Revised formula using the external table reference:

    :=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(DimDate[Date]),DimDate[Date] <= MAX(DimDate[Date])))

    Paul

    Tuesday, June 30, 2015 3:39 PM
  • Hi Paul,

    unfortunately I cannot recall where I picked that up but it has something to do with the sort order you need in order to make MAX deliver the correct results. For me it is a "no-brainer" to have a DimDate in my models (this is essential anyway once you start to work with time intelligence functions in your model). What is your problem with this approach?

    DAX looks easy at first sight, but - especially if you work with "unusual" data like yours - it isn't. At least from my perspective as a non-IT-native you need to learn it thoroughly in order to use it productivly at work. I used Rob Collies ressources (books & Online University).


    Imke

    Tuesday, June 30, 2015 4:43 PM
    Answerer
  • Its not as much an issue with the approach, I just don't want to start creating sub-tables every time I run across an issue with using formulas that there may be a formula resolution for that I just don't understand yet--due to being a novice. If the issue that I was having is basically due to needing a sub-total in order to sort correctly (I don't have the Time sub table sorted at all actually) then that is fine. But if there is something I am just not doing with formulas correctly, then it would seem easier to resolve it there.

    I have worked with other 'programming' languages in the past (ex. SAS) and there always seemed more than one way to do something. I just didn't want my lack of understanding of DAX to get in the way of the best way to do something.

    The sub table fix definitely works though!

    Also, I do have Rob's book already--I think it is necessary for DAX anyway.

    Paul

    Wednesday, July 1, 2015 3:09 PM
  • You're absolutely right about that - I'm pretty sure there is another way to do this.

    Just that I'm not the expert to know about it :-)


    Imke

    • Marked as answer by Paul-NYS Wednesday, July 1, 2015 4:08 PM
    Wednesday, July 1, 2015 3:25 PM
    Answerer