Forum Discussion

Sandeep130493's avatar
Sandeep130493
Regular Visitor
3 years ago

Show subtotal in bar chart

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_BandsCategory_1FY22 Q4FY23 Q1Grand Total  
Small TicketFOR104103207  
 HFC337242447858202  
 NBF271021988746989  
 OTH435528887243  
 PUB253151876044074  
 PVT184511322831679  
Small Ticket Total 1090507934518839514%12%
Medium TicketFOR185818253683  
 HFC5918851839111027  
 NBF8348878244161732  
 OTH393025536483  
 PUB6997454311124286  
 PVT9784182398180240  
Medium Ticket Total 31627927117158745041%42%
Large TicketFOR118771032622203  
 HFC273722767055042  
 NBF5474050098104838  
 OTH7990543913430  
 PUB10910678459187566  
 PVT141968123671265639  
Large Ticket Total 35305429566364871745%46%
Grand Total 7783836461801424563  

rebii myMicrosoft ExpertBM Anonymous Supookeed AlwaysBI Bibi PowerJ 

Need Help
Show and Tell

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

  • Sandeep130493's avatar
    Sandeep130493
    Regular Visitor
    3 years ago

    Thanks for your help brother.

    Anonymous .

     

    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 🙂