topic Re: Get % of Stacked Bar of Total For Each Bar in DAX Commands and Tips

Get % of Stacked Bar of Total For Each Bar

Anonymous — Thu, 22 Dec 2022 15:42:28 GMT

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

Re: Get % of Stacked Bar of Total For Each Bar

Anonymous — Thu, 22 Dec 2022 01:32:46 GMT

Hi Anonymous,

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

Re: Get % of Stacked Bar of Total For Each Bar

Anonymous — Thu, 22 Dec 2022 15:29:06 GMT

Hi I have updated my question to have some sample data

Re: Get % of Stacked Bar of Total For Each Bar

Anonymous — Tue, 27 Dec 2022 02:47:29 GMT

Hi Anonymous,

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

Re: Get % of Stacked Bar of Total For Each Bar

Anonymous — Tue, 27 Dec 2022 16:18:23 GMT

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

)

Re: Get % of Stacked Bar of Total For Each Bar

Anonymous — Wed, 28 Dec 2022 07:51:58 GMT

Hi Anonymous,

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