How to make GP% value display at last row ?

[admin11]

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

[v-yalanwu-msft · Accepted solution]

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:

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.

[admin11]

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

@v-yalanwu-msft 

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

can you give me hint where go wrong.

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

[admin11]

@v-yalanwu-msft 

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

Paul

[amitchandak]

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

)

[admin11]

@amitchandak 

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

Hope you can advise me where go wrong ?

How to remove those error ?

Paul