cancel
Showing results 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

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"

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

1 ACCEPTED SOLUTION
Community Support

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.
5 REPLIES 5
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.

Regards,

Xiaoxin Sheng

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

Hi I have updated my question to have some sample data

Community Support

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.
Regular Visitor

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

Community Support

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.