Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Grouping Totals / Aggr issues

I am still learning and having a terrible time with grouping totals to use for key measures.  I am having issues writing a key measure to support a Monthly Total of Story Points so that I can take the below table and calculate the % of stories points by categories for the Month.

In the data below I need to know that for the Month of July BI-Chicago had a total of 93 Story Points for the month, Then show line by line below what that % of points is so that I can take that allocation and associate a cost to it.
Line one would be 3%.   Everthing I have tried breaks or isn't grouping correctly.  

 

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi Anonymous 

    Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

    Now try this for a new calculate column with the percentage:

     

    ColPerc =
VAR Total_ =
    CALCULATE (
        SUM ( Table1[Story Points] ),
        ALLEXCEPT ( Table1, Table1[Month Year], Table1[Scrum Team Name] )
    )
RETURN
    DIVIDE ( Table1[Story Points], Total_ )

     and if you need a column with the total:

    ColPerc =
VAR Total_ =
    CALCULATE (
        SUM ( Table1[Story Points] ),
        ALLEXCEPT ( Table1, Table1[Month Year], Table1[Scrum Team Name] )
    )
RETURN
    Total_

     

2 Replies

  • v-anabat's avatar
    v-anabat
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi,

     

    Create a denominator measure as suggested below 

    Denominator = CALCULATE(SUM('Table (5)'[Story Points]), ALL('Table (5)'))
    and another measure "SumOfStoryPoints" for numerator
    SumOfStoryPoints = SUM('Table (5)'[Story Points])
    Divide these 2
    %OfStoryPoints = DIVIDE([SumOfStoryPoints],[Denominator],0)
     
    Also, if you want this data to be changed by any field use ALLExcept insetad of ALL
    Regards,
    Ananth
  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous 

    Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

    Now try this for a new calculate column with the percentage:

     

    ColPerc =
VAR Total_ =
    CALCULATE (
        SUM ( Table1[Story Points] ),
        ALLEXCEPT ( Table1, Table1[Month Year], Table1[Scrum Team Name] )
    )
RETURN
    DIVIDE ( Table1[Story Points], Total_ )

     and if you need a column with the total:

    ColPerc =
VAR Total_ =
    CALCULATE (
        SUM ( Table1[Story Points] ),
        ALLEXCEPT ( Table1, Table1[Month Year], Table1[Scrum Team Name] )
    )
RETURN
    Total_