Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
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.
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 ?
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 , 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%] )
)
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
User | Count |
---|---|
79 | |
74 | |
44 | |
32 | |
28 |
User | Count |
---|---|
100 | |
93 | |
52 | |
50 | |
48 |