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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors