Calculating percentages of subtotals

[Anonymous]

Hello!

I will first disclose that I am an occasional Power BI user, please excuse me if some necessary details are lacking, I will gladly provide whatever may be missing.

Sheet1:

Tested by	Account	Balance sheet item	Value
A	Geleistete Anzahlungen	Advance payments on inventories	150
B	Verrechnung geleiste	Advance payments on inventories	130
A	Forderungen Inland	Trade accounts receivable	400
A	Forderungen Ausland	Trade accounts receivable	350
A	Korrekturkonto Bewertung	Trade accounts receivable	820
C	Umgliederung kred. Debitoren	Trade accounts receivable	120
B	Umgliederung kred. Debitoren	Trade accounts receivable	30
D	Forderungen an deb. Kreditor	Other current assets	70
D	Forderungen an deb. Kreditor	Other current assets	50

To the point:

I would like to create a dashboard consisting of the following models:  A slicer which with the option to choose a tester (A/B/C/D) and a bar chart, representing each balance sheet item on the x-axis with percentage points on the y-axis (can also be a table).

For example, if tester A were to be chosen, the chart (or table) should display: 

-Advance payments on inventories: 53%

-Trade accounts receivable: 91%

-Other current assets: 0%

This should translate into how much each subtotal of each specific balance sheet item was reviewed by the selected tester.

I have tried to create a new column using the following DAX command "divide((Sheet1[Value],sum(Sheet1[Value])*100)" which didn't result in much of use, as it uses the overall total value to calculate a percentage. I also have tried to apply similar solutions from previous posts, although non with much success.

Any input will be greatly appreciated 🙂

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

You may try these measures.

Advance payments on inventories = 
VAR TT =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            [Balance sheet item] = "Advance payments on inventories"
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Table'[Tested by] ),
        "A",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "A"
                            && [Balance sheet item] = "Advance payments on inventories"
                    )
                ),
                TT
            ),
        "B",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "B"
                            && [Balance sheet item] = "Advance payments on inventories"
                    )
                ),
                TT
            ),
        "C",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "C"
                            && [Balance sheet item] = "Advance payments on inventories"
                    )
                ),
                TT
            ),
        "D",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "D"
                            && [Balance sheet item] = "Advance payments on inventories"
                    )
                ),
                TT
            )
    )

Other current assets = 
VAR TT =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            [Balance sheet item] = "Other current assets"
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Table'[Tested by] ),
        "A",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "A"
                            && [Balance sheet item] = "Other current assets"
                    )
                ),
                TT
            ),
        "B",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "B"
                            && [Balance sheet item] = "Other current assets"
                    )
                ),
                TT
            ),
        "C",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "C"
                            && [Balance sheet item] = "Other current assets"
                    )
                ),
                TT
            ),
        "D",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "D"
                            && [Balance sheet item] = "Other current assets"
                    )
                ),
                TT
            )
    )

Trade accounts receivable = 
VAR TT =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            [Balance sheet item] = "Trade accounts receivable"
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Table'[Tested by] ),
        "A",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "A"
                            && [Balance sheet item] = "Trade accounts receivable"
                    )
                ),
                TT
            ),
        "B",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "B"
                            && [Balance sheet item] = "Trade accounts receivable"
                    )
                ),
                TT
            ),
        "C",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "C"
                            && [Balance sheet item] = "Trade accounts receivable"
                    )
                ),
                TT
            ),
        "D",
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        [Tested by] = "D"
                            && [Balance sheet item] = "Trade accounts receivable"
                    )
                ),
                TT
            )
    )

If A is chosen, the chart is as follows.

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , basis of percent of subtotal is not clear

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

[Anonymous]

Hi @amitchandak thank you for the response.

The basis of percentage should be the total value of each balance sheet item by itself.

For example, it is 280 in the case of "Advance payment inventories" and 1720 in "Trade accounts receiveables".