Get % of Stacked Bar of Total For Each Bar

[mthomas-powerbi]

Hello, I have this Stacked Bar, and I'm trying to get % for each series compared to that month's total. I'm pretty new and having trouble trying many ways, but unable to match my current SSRS graph that I'm trying to convert to Power BI.

So in this chart, I'm trying to get % of the grey area compared to the month's total.

This will be in tool tip

So For December, it would be around 86% taking "total / amount in the grey"

So here is some sample data, there is a date column, but I only use the Year and Month from it. I was % for each program type, for against the total of the month.

AmountDue	Year	Month	Type	PercentTotalByMonth
1923.03	2022	Dec	Lease	0.617356
46003.13	2022	Dec	Reagents Only	14.76852
263568.32	2022	Dec	Reagent Rental	84.61412

How % column calculate here Amount Due / Total For The Month

EX: 263568.32 / 311494.48 = 0.84614 * 100 = 84.61412

[v-shex-msft]

Hi @mthomas-powerbi,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[mthomas-powerbi]

Hi I have updated my question to have some sample data

[v-shex-msft]

Hi @mthomas-powerbi,

Please try to use the following calculated column formula if it suitable for your requirement:

Percent =
DIVIDE (
    CALCULATE (
        SUM ( Table[AmountDue] ),
        FILTER (
            Table,
            [Year] = EARLIER ( Table[Year] )
                && [Month] = EARLIER ( Table[Month] )
                && [Type] = EARLIER ( Table[Type] )
        )
    ),
    CALCULATE (
        SUM ( Table[AmountDue] ),
        FILTER (
            Table,
            [Year] = EARLIER ( Table[Year] )
                && [Type] = EARLIER ( Table[Type] )
        )
    )
)

Regards,

Xiaoxin Sheng

[mthomas-powerbi]

Hi Very close, but the percentage is about 6 percent off for this case

Here is what I have for column

Percent =

DIVIDE (

    CALCULATE (

        SUM( ViewCMPL_ReconciliationInvoices[AMOUNT_DUE_ORIGINAL] ),

        FILTER (

            ViewCMPL_ReconciliationInvoices,

            [TRX_DATE].[Year] = EARLIER( ViewCMPL_ReconciliationInvoices[TRX_DATE].[YEAR] )

                && [TRX_DATE].[Month] = EARLIER( ViewCMPL_ReconciliationInvoices[TRX_DATE].[Month] )

                && [PROGRAM_TYPE] = EARLIER( ViewCMPL_ReconciliationInvoices[PROGRAM_TYPE] )

        )

    ),

    CALCULATE(

        SUM ( ViewCMPL_ReconciliationInvoices[AMOUNT_DUE_ORIGINAL] ),

        FILTER (

            ViewCMPL_ReconciliationInvoices,

            [TRX_DATE].[Year] = EARLIER (ViewCMPL_ReconciliationInvoices[TRX_DATE].[YEAR])

                    && [PROGRAM_TYPE] = EARLIER (ViewCMPL_ReconciliationInvoices[PROGRAM_TYPE] )

        )

    )

)

[v-shex-msft · Accepted solution]

Hi @mthomas-powerbi,

I suppose these results are aggregate multiple records on your chart, they should get the correct values on the detail level.

Perhaps you can try to switch to measure formula to directly calculate the values on chart without additional aggregation.

Percent =
VAR currDate =
    MAX ( iewCMPL_ReconciliationInvoices[TRX_DATE] )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( ViewCMPL_ReconciliationInvoices[AMOUNT_DUE_ORIGINAL] ),
            FILTER (
                ALLSELECTED ( ViewCMPL_ReconciliationInvoices ),
                YEAR ( [TRX_DATE] ) = YEAR ( currDate )
                    && MONTH ( [TRX_DATE] ) = MONTH ( currDate )
            ),
            VALUES ( ViewCMPL_ReconciliationInvoices[PROGRAM_TYPE] )
        ),
        CALCULATE (
            SUM ( ViewCMPL_ReconciliationInvoices[AMOUNT_DUE_ORIGINAL] ),
            FILTER (
                ALLSELECTED ( ViewCMPL_ReconciliationInvoices ),
                YEAR ( [TRX_DATE] ) = YEAR ( currDate )
            ),
            VALUES ( ViewCMPL_ReconciliationInvoices[PROGRAM_TYPE] )
        )
    )

Regards,

Xiaoxin Sheng