Show subtotal in bar chart

[Sandeep130493]

Hello Experts,

I want to show subtotal % of categoary like below in my bi report

We are manully copying below data based on categoary and paste in the background of above chart.

I am able to to make it till catogary. not sure how to show subtotal in bar chart like above excel dashbaord.

Below is sample data for your reference.

I want to show small ticket total,Medium ticket total,Large ticket total % (Small Ticket Total / Grand Total )

) in bar chart in below powerbi chart .same like excel above chart.

Ticket_Bands	Category_1	FY22 Q4	FY23 Q1	Grand Total
Small Ticket	FOR	104	103	207
	HFC	33724	24478	58202
	NBF	27102	19887	46989
	OTH	4355	2888	7243
	PUB	25315	18760	44074
	PVT	18451	13228	31679
Small Ticket Total		109050	79345	188395	14%	12%
Medium Ticket	FOR	1858	1825	3683
	HFC	59188	51839	111027
	NBF	83488	78244	161732
	OTH	3930	2553	6483
	PUB	69974	54311	124286
	PVT	97841	82398	180240
Medium Ticket Total		316279	271171	587450	41%	42%
Large Ticket	FOR	11877	10326	22203
	HFC	27372	27670	55042
	NBF	54740	50098	104838
	OTH	7990	5439	13430
	PUB	109106	78459	187566
	PVT	141968	123671	265639
Large Ticket Total		353054	295663	648717	45%	46%
Grand Total		778383	646180	1424563

@rebii @myMicrosoft @ExpertBM @Anonymous @Supookeed @AlwaysBI @Bibi @PowerJ 

[Sandeep130493]

Thanks for your help brother.

@v-yangliu-msft .

I wanted to show top 3 Category of each brand with subtotal. like showing below in excel highlighted

I am using dense rank function 

Ranks_Wise =

 Var a = RANKX(ALLEXCEPT('Table 2','Table 2'[Ticket_Bands]),

CALCULATE('Table 2'[Q4]

,ALLEXCEPT('Table 2','Table 2'[Category_1])),,DESC,Dense)

Return

a

I want show top 3 category of each brand using rank . but want to show top 3 and small ticket subtotal / Medium and large ticket . like showing in above excel screenshot

 Thanks for your help 🙂

[v-yangliu-msft]

Hi  @Sandeep130493 ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
CROSSJOIN(
DISTINCT('Table'[Ticket_Bands]),
UNION(
DISTINCT('Table'[Category_1]),
DISTINCT('Table'[Ticket_Bands])))

2. Create measure.

Cate Q1 =
var _sum1=
SUMX(FILTER(ALL('Table') ,
'Table'[Ticket_Bands]=MAX('Table 2'[Ticket_Bands])&&'Table'[Category_1]=MAX('Table 2'[Category_1])),[FY23 Q1])
var _sum2=
SUMX(FILTER(ALL('Table') ,
'Table'[Ticket_Bands]=MAX('Table 2'[Ticket_Bands])),[FY23 Q1])
return
DIVIDE(_sum1,_sum2)

Cate Q4 =
var _sum1=
SUMX(FILTER(ALL('Table') ,
'Table'[Ticket_Bands]=MAX('Table 2'[Ticket_Bands])&&'Table'[Category_1]=MAX('Table 2'[Category_1])),[FY22 Q4])
var _sum2=
SUMX(FILTER(ALL('Table') ,
'Table'[Ticket_Bands]=MAX('Table 2'[Ticket_Bands])),[FY22 Q4])
return
DIVIDE(_sum1,_sum2)

Ticket Q1 =
var _sum1=
SUMX(FILTER(ALL('Table') ,
'Table'[Ticket_Bands]=MAX('Table 2'[Ticket_Bands])),[FY23 Q1])
var _sum2=
SUMX(ALL('Table')
,[FY23 Q1])
return
DIVIDE(_sum1,_sum2)

Ticket Q4 =
var _sum1=
SUMX(FILTER(ALL('Table') ,
'Table'[Ticket_Bands]=MAX('Table 2'[Ticket_Bands])),[FY22 Q4])
var _sum2=
SUMX(ALL('Table')
,[FY22 Q4])
return
DIVIDE(_sum1,_sum2)

Q1 =
SWITCH(
    TRUE(),
   MAX('Table 2'[Ticket_Bands]) =MAX('Table 2'[Category_1]) ,[Ticket Q1],
'Table'[Cate Q1])

Q4 =
SWITCH(
    TRUE(),
   MAX('Table 2'[Ticket_Bands]) =MAX('Table 2'[Category_1]) ,[Ticket Q4],
'Table'[Cate Q4])

3. Close Show items with no data.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Sandeep130493 , You might have added an additional item in dimension or extended dimension

https://amitchandak.medium.com/power-bi-add-grand-total-row-in-the-bar-visual-44c3d1d463be

or

Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU