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 th...
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_

     

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