Forum Discussion

SoufTC's avatar
SoufTC
Helper I
4 years ago
Solved

Subtotals in Matrix with Hierarchy not adding up

I’m having trouble to sum totals of subcategory   I have two tables. 1. A table that defines unique FaceBook pages names (repository)[Page] and the name of the person responsible for the page(repo...
Need Help
Show and Tell
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, SoufTC 

    Try measures as follows:

    Likes Rank = 
var _t=FIlter(ALL('repository'),[Last Likes]<>BLANK())
var _rank=
RANKX(_t,
    CALCULATE([Last Likes]),,ASC,Dense)
    return IF([Last Likes]<>BLANK()&&HASONEVALUE(repository[Page]),_rank)
    Followers Rank = 
var _t=FIlter(ALL('repository'),[Last Followers]<>BLANK())
var _rank=
RANKX(_t,
    CALCULATE([Last Followers]),,ASC,Dense)
    return IF([Last Followers]<>BLANK()&&HASONEVALUE(repository[Page]),_rank)
    COMMUNITY = 
SUMX(ADDCOLUMNS('repository',"_last rank",[Likes Rank],"_followers rank",[Followers Rank]),DIVIDE([_last rank]+[_followers rank],2))

    Result:

    Please refer to the attachment below for details.

    Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-angzheng-msft's avatar
v-angzheng-msft
Community Support
4 years ago

Hi, SoufTC 

From what I've got, the last follow on P3 should be 80.

Based on the information above, I create the community measure easily as follows:

COMMUNITY = 
([Last Followers]+[Last Likes])/2

this seems to work fine.

Result:

Please refer to the attachment below for details.

Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

SoufTC's avatar
SoufTC
Helper I
4 years ago

v-angzheng-msft  thank you for your help ! but I made a mistake in the results table (I corrected it) I'm trying to calculate [community] using the rank like this : COMMUNITY = ([Likes Rank] + [Followers Rank])/2 ; but unfortunately I can't get the right total 

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, SoufTC 

    Try measures as follows:

    Likes Rank = 
var _t=FIlter(ALL('repository'),[Last Likes]<>BLANK())
var _rank=
RANKX(_t,
    CALCULATE([Last Likes]),,ASC,Dense)
    return IF([Last Likes]<>BLANK()&&HASONEVALUE(repository[Page]),_rank)
    Followers Rank = 
var _t=FIlter(ALL('repository'),[Last Followers]<>BLANK())
var _rank=
RANKX(_t,
    CALCULATE([Last Followers]),,ASC,Dense)
    return IF([Last Followers]<>BLANK()&&HASONEVALUE(repository[Page]),_rank)
    COMMUNITY = 
SUMX(ADDCOLUMNS('repository',"_last rank",[Likes Rank],"_followers rank",[Followers Rank]),DIVIDE([_last rank]+[_followers rank],2))

    Result:

    Please refer to the attachment below for details.

    Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • SoufTC's avatar
      SoufTC
      Helper I
      4 years ago

      v-angzheng-msft this is exactly what I am looking for thank you !