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 pu...
Need Help
DataInsights's avatar
DataInsights
Super 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 ()
)