Forum Discussion

JackCarter's avatar
JackCarter
Frequent Visitor
2 years ago

Calculation Group or Measure?

I need to replicate the below in power BI but I am having trouble with the measure to create the % column and I am not sure if I need to have a calculation group or a measure to do so. Very simply put the Percent value for 31011 for example is total balance of 31011 divided by the total of 31010, simple. However to further complicate it, 31017 % is total balance of 31017 divided by total balance of 31016. So essentially the calculation needed for each row depends on the row value (Ledger Account). I have tried the below measure but it says it doesn't have enough memory to complete when I add it to a matrix for example. Any help would be much appreciated!

 

 

% = VAR GL = SELECTEDVALUE('GL FACT (Combined) (ABV)'[GL ACCOUNT])
VAR Perc = Switch(TRUE(),
GL="31011",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31011")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31012",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31012")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31017",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31017")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31016"))),
GL="31018",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31018")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31016"))),
GL="31040",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31040")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31041",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31041")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31040"))),
GL="31042",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31042")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31040"))),
GL="31043",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31043")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31044",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31044")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31043"))),
GL="31045",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31045")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31043"))),
GL="31054",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31054")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31055",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31055")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31054"))),
GL="31056",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31056")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31054"))),
GL="31080",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31056")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31107",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31056")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31130",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31056")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31131",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31056")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31133",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31056")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31134",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31056")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31136",DIVIDE(CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]="31056")),CALCULATE([BRL Amount],Filter(ALL('GL FACT (Combined) (ABV)'[GL ACCOUNT]),'GL FACT (Combined) (ABV)'[GL ACCOUNT]= "31010"))),
GL="31010",BLANK(),
GL="31013",BLANK(),
GL="31014",BLANK(),
GL="31015",BLANK(),
GL="31050",BLANK()
)
Return
Perc

 

 

 

Need Help

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    JackCarter,

     

    Have you tried shifting any of the logic upstream? For example, you could create calculated columns in the fact table that precompute the numerator and denominator. The logic in the first argument of DIVIDE would be moved to calculated column [PERC NUMERATOR] and the second argument of DIVIDE would be move to calculated column [PERC DENOMINATOR]. Then create a SUM measure for each calculated column. The excerpt below

     

    % =
VAR GL =
    SELECTEDVALUE ( 'GL FACT (Combined) (ABV)'[GL ACCOUNT] )
VAR Perc =
    SWITCH (
        TRUE (),
        GL = "31107",
            DIVIDE (
                CALCULATE (
                    [BRL Amount],
                    FILTER (
                        ALL ( 'GL FACT (Combined) (ABV)'[GL ACCOUNT] ),
                        'GL FACT (Combined) (ABV)'[GL ACCOUNT] = "31056"
                    )
                ),
                CALCULATE (
                    [BRL Amount],
                    FILTER (
                        ALL ( 'GL FACT (Combined) (ABV)'[GL ACCOUNT] ),
                        'GL FACT (Combined) (ABV)'[GL ACCOUNT] = "31010"
                    )
                )
            ),
        GL = "31050", BLANK ()
    )
RETURN
    Perc

     

    can be rewritten as

     

    % =
SWITCH (
    SELECTEDVALUE ( 'GL FACT (Combined) (ABV)'[GL ACCOUNT] ),
    "31107", DIVIDE ( [Perc Numerator], [Perc Denominator] ),
    "31050", BLANK ()
)