Forum Discussion

SoufTC's avatar
SoufTC
Icon for Helper I rankHelper 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.

SoufTC's avatar
SoufTC
Icon for Helper I rankHelper 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
Icon for Community Support rankCommunity 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
    Icon for Helper I rankHelper I
    4 years ago

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