Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum depending on hierarchy level

Hi everyone,

I have a date hierarchy (Year, Month) and a fact (numbers). What I would like to do may sound simple, but I just cannot find a way.

When the Hierarchylevel "Year" is displayed, I would like to sum only the facts which have month = 12 and display them in the chart.
When the Hierarchylevel "Month" is displayed, I would like to sum the facts by the month.

Is something like this possible?

 

Thank you in advance
J.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I have found a solution which I would like to share:

     

    I used this formula

    MeasureName = CALCULATE(SUM('tableName'[factName]);LASTDATE('tableName'[DateColumn]))

    Together with a bar chart having on the x axis the DateColumn Hierarchy (in my case only Year and Month).

    The result is, that I have on the highest level the years in bars, where only the last month (December/12 for a closed year or the last month of the current year) is used for calculation. If I drill down one level, I see all the months for this specific year. 

    Thanks to everyone for your time and your help!

    Best

    J

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous if you share sample data with expected output, it will help. 

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    I tried something like this for my last period calculation. Just see if this can help

     

    Last Period Employee = 
var _min_date = minx(all('Date'),'Date'[Date])
var _Expression=if(ISFILTERED('Date'[Month Year]),maxx('Date',DATEADD('Date'[Date],-1,MONTH)),maxx('Date',DATEADD('Date'[Date],-1,YEAR)))
Return
 CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=_Expression && Employee[Start Date]>=_min_date && (ISBLANK(Employee[End Date]) || Employee[End Date]>_Expression)),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

     

    Is filter to switch month and year.

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog -

    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970


    https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739

    https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Could "Date Hierarchy" satisfy your requirements?

    If not, you could provide sample data without any confidential information and explain your additional requirements.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-eachen-msft ,

       

      thank you for that, but this is not what I am looking for. I already have a date hierarchy (Year and Month) but as stated in my post below, I would like to calculate in the year perspective only the month = 12 and in the month perspective each month seperately.

       

      Best

      J

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I have found a solution which I would like to share:

         

        I used this formula

        MeasureName = CALCULATE(SUM('tableName'[factName]);LASTDATE('tableName'[DateColumn]))

        Together with a bar chart having on the x axis the DateColumn Hierarchy (in my case only Year and Month).

        The result is, that I have on the highest level the years in bars, where only the last month (December/12 for a closed year or the last month of the current year) is used for calculation. If I drill down one level, I see all the months for this specific year. 

        Thanks to everyone for your time and your help!

        Best

        J

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello everyone,

    thank you amitchandak, parry2k, v-eachen-msft. for your considerations.

    Unfortunately, I could not make your code run. 

    I would like to share with you some example data and explain the requirement in greater detail:

     

    Here, you can see the data. 
    What I would like to have is a stacked Bar chart with the hierarchy (Month and Year). First, the year is shown (2019) and here I would like to have only the month 12 summed. When I click on the year 2019 (meaning I will show the months), I would like to have all months summed up.

    So, the result should be

    2019: CEO = 11, Sales = 8, Production = 3, ProductionUS = 3, SalesUS = 8

    After clicking on the bar 2019, I would like to have 12 stacked bars (month 1-12) with the values, summed by categorie1 and categorie2:

    month 1 = ProductionUS = 45, SalesUS = 25, CEO = 70, Sales = 25, Production = 45
    month 2 = ProductionUS = 43, SalesUS = 24, CEO = 67, Sales = 24, Production = 43

    and so on...

     

    I hope I expressed it in a more clear way now. Please let me know if you need any further details and please apalogize my late reply.

     

    Thank you in advance

    Best

    J

    Sample data