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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
admin11
Memorable Member
Memorable Member

How to make GP% value display at last row ?

Hi All,

 

I have below Table :-

 

SEGMENT_C............. SALES_.................SALES GP%

Other..............................24306..................32%

Related Coy.................4450.....................47%

Total ..............................28,756.................34 %

 

How to make it display as below :-

 

SEGMENT_C............. SALES_

Other..............................24306

Related Coy.................4450

Total ...............................28,756

GP % .............................34%

 

I know that in same column , unable to display sales and GP % value.  Can some one suggestion some thing very closed to it.

 

Below is my PBI sample file :-

 

https://www.dropbox.com/s/ageeb7resqpsysk/PBT%20Create%20Christine%20Ho%20report.pbix?dl=0

 

 

Thank you

 

Paul

 

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @admin11 ;

You could add rows in your [Segement_c new] column , then modify the measure.

1.add rows: (could add it in power query ,here i chang it by the column dax)

Segement_c new = 
Switch ( True() ,
[Segment_c] ="Related Coy" ,"Related Coy",
[Segment_c] =BLANK(),"GP%",
"Other")

 2.change the [Sales_] measure.

SALES_ = 
IF(ISINSCOPE('SEGMENT'[Segement_c new]),
IF( MAX('SEGMENT'[Segement_c new])="GP%",
FORMAT( CALCULATE( DIVIDE((SUM(SALES[sales]) - SALES[COST_]),SUM(SALES[sales])),FILTER(ALL('SEGMENT'),[SEGMENT_C]<>BLANK())),"0.0%"), 
FORMAT( SUM(SALES[sales]),"0")),CALCULATE(SUM(SALES[sales]),FILTER('SEGMENT',[SEGMENT_C]<>BLANK())))
SALES GP% = DIVIDE((SUM(SALES[sales]) - SALES[COST_]),SUM(SALES[sales]))

The final output is shown below:

vyalanwumsft_0-1639719361095.png

 

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

6 REPLIES 6
v-yalanwu-msft
Community Support
Community Support

Hi, @admin11 ;

You could add rows in your [Segement_c new] column , then modify the measure.

1.add rows: (could add it in power query ,here i chang it by the column dax)

Segement_c new = 
Switch ( True() ,
[Segment_c] ="Related Coy" ,"Related Coy",
[Segment_c] =BLANK(),"GP%",
"Other")

 2.change the [Sales_] measure.

SALES_ = 
IF(ISINSCOPE('SEGMENT'[Segement_c new]),
IF( MAX('SEGMENT'[Segement_c new])="GP%",
FORMAT( CALCULATE( DIVIDE((SUM(SALES[sales]) - SALES[COST_]),SUM(SALES[sales])),FILTER(ALL('SEGMENT'),[SEGMENT_C]<>BLANK())),"0.0%"), 
FORMAT( SUM(SALES[sales]),"0")),CALCULATE(SUM(SALES[sales]),FILTER('SEGMENT',[SEGMENT_C]<>BLANK())))
SALES GP% = DIVIDE((SUM(SALES[sales]) - SALES[COST_]),SUM(SALES[sales]))

The final output is shown below:

vyalanwumsft_0-1639719361095.png

 

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

@v-yalanwu-msft 

from below image , when i filter year = 2021 , i get the sales total amount is correct. are you able to help me make your total amount same as mind ?

admin11_0-1640335268176.png

 

@v-yalanwu-msft 

 Try to follow your foot step on my actual PBI file , but the total amount wrong. see the image below.

admin11_0-1640332866381.png

can you give me hint where go wrong.

 

Below image is what you did , both total is correct.

 

admin11_0-1640333475752.png

 

@v-yalanwu-msft 

Wow you are very good , i am surprise you can doing this . many thank.

Paul

amitchandak
Super User
Super User

@admin11 , You can not display like that with standard visual , You need to create a custom table with these values and display

 

Union (

Summarize(Table, Table[Segment_C], "_sales", sum(Table[Sales_])  ),

Summarize(Table, "Segment_C", "Total", "_sales", sum(Table[Sales_])  ),

Summarize(Table, "Segment_C", "GP%", "_sales", [GP%]  )

)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak 

I try to create a new table for your propose union command , i get below error :-

admin11_0-1639639308788.png

Hope you can advise me where go wrong ?

How to remove those error ?

 

Paul

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.