Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mthomas-powerbi
Regular Visitor

Get % of Stacked Bar of Total For Each Bar

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"

mthomaspowerbi_0-1671661713824.png

 

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.

 

 

AmountDueYearMonthTypePercentTotalByMonth
1923.032022DecLease0.617356
46003.132022DecReagents Only14.76852
263568.322022DecReagent Rental84.61412

 

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

EX: 263568.32 / 311494.48 = 0.84614 * 100 = 84.61412

 

 

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi I have updated my question to have some sample data

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

mthomaspowerbi_0-1672157879832.png

 

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] )
        )
    )
)

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors