Create a chart: Returns as a percentage of sales by "category"

[dmcgetti]

Good morning,

I am just starting out with Power BI and I am stuck...

I have a sales database and I would like to show returns as a percentage of total sales.  My issue is that I am pulling my returns based on a column called "ReturnCode".  There are approx. 26 different return codes and would like to group them in to 3 different categories and have those categories be the chart that shows that category as the percentage of total sales.  I have figured out how to make a measure for one return code:

Percent of Sale = SUMX(FILTER('bitnami_wordpress data','bitnami_wordpress data'[ReturnCode]="F"),'bitnami_wordpress data'[TotalSale]) / SUMX(FILTER('bitnami_wordpress data','bitnami_wordpress data'[ReturnCode]=""),'bitnami_wordpress data'[TotalSale])

But I don't know how to group them.  Below is a sample of my data:

Thanks in advance!

[Anonymous]

Hi @dmcgetti ,

Can you please explain more about how to group these return codes?(e.g. expected result, sample data)

Regards,

Xiaoxin Sheng

[dmcgetti]

Here an example if I wanted Return Codes A and B grouped together:

INVOICE	SALE	RETURN CODE
123	100
456	15
789	20	A
234	7	B
567	5	C
890	25	D

I would have a graph that would have a bar that would show 115 for no Return Code, 27 for A/B, 5 for C and 25 for D.

Does that makes sense?

[Anonymous · Accepted solution]

Hi @dmcgetti ,

You can add a calculate column to return group based on sales, then write a measure to calculate percent on current group:

Group=
SWITCH([Sale],20,"A",7,"B",5,"C",25,"D","")

Measure =
VAR currGroup =
    SELECTEDVALUE ( Table[Group] )
RETURN
    IF (
        currGroup IN { "A", "B", "C", "D" },
        DIVIDE (
            CALCULATE (
                SUM ( Table[TotalSale] ),
                ALLSELECTED ( Table ),
                VALUES ( Table[Group] )
            ),
            CALCULATE (
                SUM ( Table[TotalSale] ),
                FILTER ( ALLSELECTED ( Table ), Table[Group] = "" )
            ),
            BLANK ()
        )
    )

Regards,

Xiaoxin Sheng

[dmcgetti]

@Anonymous Exactly what I was looking for thank you.