none
DAX:- Requried Percentage Sum Measure for Hirarchy Levels RRS feed

  • Question

  • Hi I Have a table like this in Tabular Model

    Id InvestmentCode Parent RootParent Level RowNumber RollUpto Percentage
    9 AAA NULL 9 0 1096 NULL NULL
    224 BBB 9 9 10 1097 1096 100.00%
    671 CCC 224 9 60 1099 1097 75.00%
    522 DDD 224 9 60 1098 1097 25.00%
    818 EEE 522 9 61 1100 1098 100.00%
    944 FFF 818 9 70 1102 1100 99.00%
    943 GGG 818 9 70 1104 1100 1.00%
    946 HHH 944 9 71 1106 1102 100.00%

    Requried  Percentage Sum Measure for  Below output format 

    Row Labels PercetageMeasure
    AAA                    
    BBB 100.00%
    CCC 75.00%
    DDD 25.00%
    . .
    . .
    . .



    • Edited by Rangareddydt Wednesday, December 9, 2015 11:50 AM
    Wednesday, December 9, 2015 11:48 AM

All replies

  • This seems rather trivial... what about

    PercentageMeasure:=AVERAGE(Table[Percentage])

    ?

    Wednesday, December 9, 2015 1:33 PM
  • My understanding is that DAX does not support parent-child hierarchies (self referenced) directly.

    Think about flattening it.

    Check this pattern from SQLBI.

    http://www.daxpatterns.com/parent-child-hierarchies/


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Wednesday, December 9, 2015 1:51 PM
  • Hi Rangareddydt,

    According to your description, I'm not clear about your requirement, because I can't understand your expect result.

    Since you already have the Percentage column in your table, if you just want to show the percentage on InvestmentCode level, you can apply expression like:

    PercetageMeasure:= CALCULATE(SUM('Table'[Percentage]), ALL('Table'[InvestmentCode]))

    I assume your percentage calculation is based on some two column values. If each InvestmentCode also has detail level rows, you may want to get the percentage of the total on InvestmentCode level. If that is your requirement, please use expression below:

    PercetageMeasure:= DIVIDE(

    CALCULATE(SUM('Table'[Percentage])),

    CALCULATE(SUM('Table'[Percentage]), ALL('Table'[InvestmentCode]))

    )

    To do the aggregation on different hierarchy level, you just need to apply filters in CALCULATE(). For more information about the usage of CALCULATE(), please refer to article below:

    How CALCULATE works in DAX 

    Regards,


    Simon Hou
    TechNet Community Support


    Thursday, December 10, 2015 12:38 PM
    Moderator