% of static parent value in matrix

[goosefrabah]

In the 3rd column of my matrix, I need the child values of "t100" to be calculated as a % of the parent of "test 2".

The parent value of 7.67% is correct, but the first child should be (5.5/131.3)*100 = 4.2%. i.e the sum of each child value % should equate to their respective parent value. 

For context, the test 2 column is a calculated value which ignores visual level filters applied to the matrix:

test 2 = CALCULATE(SUM('Table'[Value], ALL('Table2'))

In an ideal scenario, all the parent values (131.3B) should fill down to all child values.

Any help is appreciated. Thanks.

[amitchandak]

@goosefrabah , checkout new Visual calculation COLLAPSE

https://medium.com/@amitchandak/understanding-visual-calculations-in-power-bi-revolutionizing-data-a...

Otherwise, you have to use isinscope and and switch calculation at each level

How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA

Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

[goosefrabah]

Hi,

The ISINSCOPE solution is getting me on the right track. However, there are 4 fields as rows, and each level in the heirarchy is calculating the % of its parent (as expected), when I would like each level to calculate as a % of the top most level.

My measure so far:

isinscope test = 
SWITCH (
TRUE (),
ISINSCOPE ( 'Mapping Table 2'[Row2] ),
DIVIDE (
'Main'[t100],
CALCULATE ( [test 2], ALL ( 'Mapping Table 2' ) )
),
ISINSCOPE ( 'Mapping Table 3'[Row 3] ),
DIVIDE (
'Main'[t100],
CALCULATE ( [test 2], ALL ( 'Mapping Table 3' ) )
),
ISINSCOPE ( 'Mapping Table 4'[Row 4] ),
DIVIDE (
'Main'[t100],
CALCULATE ( [test 2], ALL ( 'Mapping Table 4' ) )
),
DIVIDE ( 'Main'[t100], [test 2] )
)

[v-yiruan-msft]

Hi @goosefrabah ,

Please check the following blog, hope it can help you resolve the problem.

Use IsInScope to get the right hierarchy level in DAX - Kasper On BI

Measure =
VAR grandtotal =
    CALCULATE ( [Sum of Value], Sellers[IsTotal] = 3 )
VAR sellersum =
    CALCULATE ( [Sum of Value], Sellers[IsTotal] = 0 )
VAR sellerselected =
    ISINSCOPE ( sellers[Seller] )
VAR channelselected =
    ISINSCOPE ( sellers[channel] )
VAR Countrysum =
    CALCULATE (
        [Sum of Value],
        Sellers[IsTotal] = 1,
        ALLEXCEPT ( Sellers, Sellers[COUNTRY] )
    )
VAR Countryselected =
    ISINSCOPE ( sellers[Country] )
VAR Regionsum =
    CALCULATE (
        [Sum of Value],
        Sellers[IsTotal] = 2,
        ALLEXCEPT ( Sellers, Sellers[Region] )
    )
VAR Regionselected =
    ISINSCOPE ( sellers[Region] )
RETURN
    SWITCH (
        TRUE (),
        sellerselected, sellersum,
        channelselected, BLANK (),
        countryselected, Countrysum,
        regionselected, Regionsum,
        grandtotal
    )

If the above one can't help you get the expected result, please provide some raw data in your table 'Mapping Table 2' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[goosefrabah · Accepted solution]

This set me on the right track. Here is the measure I am using, and its correct output:

% of total (ignoring filters) =

VAR _pct = DIVIDE( 'Fact'[t100], CALCULATE( [test 2], ALLSELECTED()))
// 'test 2' is a simple measure that calculates a sum, ignoring visual level filters I have applied using ALL()
RETURN

IF( ISINSCOPE( 'Mapping Table2[Row2] ), _pct, DIVIDE('Fact'[t100], [test 2]))