grand total division instead of sum

[Anonymous]

Hi,

I have a simple fact table with different transactions and values.

And just one measure that sums the "Value" column:

qty = sum(transactiontable[Value])

when I draw a matrix table I filter it "Category2" by "liquid"and "production"

Showing the folling result:

I was wondering is there is any way to show the division between waste & activity for each branch/plant as it is shown in the image bellow:

Im also attaching a link to the PBI model is case it helps

https://www.dropbox.com/s/lgfkjf40csfc6t4/grand%20total%20by%20branch%20plant.pbix?dl=0 

Thank you very much for you support

[Anonymous]

Thanks again @amitchandak ! I got it with your help.

Isinscope2 =
SWITCH (
    TRUE (),
    ISINSCOPE ( transactiontable[Category2] ), sum(transactiontable[Value]),
    ISINSCOPE ( transactiontable[Category] ), sum(transactiontable[Value]),
    FORMAT (
        DIVIDE (
            CALCULATE (
                sum(transactiontable[Value]),
                transactiontable[Category2] = "Production"
            ),
            CALCULATE (
                sum(transactiontable[Value]),
                Movimientos[Category] = "Liquid"
            )
        ),
        "Percent"
    )
)

[Anonymous]

thanks for the tip @amitchandak !! I will try!!

[amitchandak · Accepted solution]

@Anonymous , You can create a measure for that and force that plant level using isinscope.

The column of Activity , Waste should not be in scope

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/