How can I sum up total sales that include sales and refunds in my data?

[ianwuk]

Hello All.

Can anyone kindly help me with my issue?

I have this data:

Date	Sale	Commission	Type	Name
1st March 2018	68	33.19	Sale	John
1st March 2018	149	75.32	Sale	Jane
1st March 2018	68	33.19	Sale	John
1st March 2018	68	33.19	Refund	John
1st March 2018	149	75.32	Refund	Jane

How can I sum up total sales for each name that also minuses the refunds?  I know I need to use SumX (I think), but I am not sure how?

So, as an example, to calculate the sum of total sales for March 1st 2018 for John it should be:

68 (Sale) + 68 (Sale) - 68 (Refund) = 68 (Total Sales for John for 1st March 2018)

And then work it out the same way for all other employees (e.g. Jane).

I also want to do the same for commissions too, can anyone help me please?

Many thanks.

[Zubair_Muhammad · Accepted solution]

HI @ianwuk

Try this MEASURE

Total Sales =
CALCULATE ( SUM ( TableName[Sale] ), TableName[Type] = "Sale" )
    - CALCULATE ( SUM ( TableName[Sale] ), TableName[Type] = "Refund" )

[ianwuk]

Hello @Zubair_Muhammad.

Thanks very much.   I will try this tomorrow and let you know.

On a related note, how can I group the data in my example to look like this (for a separate table).

        Day                               Name                     Total Sales    Total Refunds
1st March 2018                      John                             2                   1

1st March 2018                      Jane                              1                   1

It needs to be grouped by Name, but I don't know how to also count number of Sales and number of Refunds and group that as well to produce the above. 

Thanks.

[Zubair_Muhammad]

@ianwuk

you can use a MEASURE like

Total Sales Count =
CALCULATE ( COUNTROWS ( TableName ), TableName[Type] = "Sale" )

[Zubair_Muhammad]

@ianwuk

Please see the attached file here

[ianwuk]

Thanks so much for this @Zubair_Muhammad!

I am nearly there now.

I do have one more task, how can I count the days between these two date columns and display it as a whole number?  I want to see how many days it takes for a refund to be generated after a product has been ordered.

The data looks like this:

Order Date                                       Refund Date

Thursday March 8th 2018               Thursday March 8th 2018
Wedndesday March 7th 2018         Thursday March 8th 2018

It should look like this:

Order Date                                       Refund Date                            Days to Refund

Thursday March 8th 2018               Thursday March 8th 2018            0
Wedndesday March 7th 2018         Thursday March 8th 2018            -1

I tried other solutions on this forum but the calculations are not what I expect to see.  Can you help at all?

For example, if I set up a new column to minus order date from refund date it is not what I expect to see above.

I set up a column like this:

Column = AffiliatesMarch2018[Order Date].[Day] - AffiliatesMarch2018[Refund Date].[Day]

It looks like this:



What is circled in blue looks correct, and is what I want, but what is calculated and circled in red looks wrong?

How can I also make it so that for any order that has  no refund date it is blank and only the orders with a refund date have a value in the new column?

Many thanks.

[ianwuk]

Hello @Zubair_Muhammad, I solved the above request.

Now I have one more problem please.

I created this new measure:

Refund % = AffiliatesMarch2018[Total Refunds Count]/AffiliatesMarch2018[Total Sales Count]*100

However, it results in some records in the column saying "Infinity" if no sales are made for example, but there is still a refund.

How can I remove "Infinity" and make it blank?

When I tried creating an IF measure it said that it could not be done because it involced text and numbers.

My measure looked like this:

Measure = IF (AffiliatesMarch2018[Total Refunds Count]/AffiliatesMarch2018[Total Sales Count]*100 = "Infinity", BLANK (),
    AffiliatesMarch2018[Total Refunds Count]/AffiliatesMarch2018[Total Sales Count]*100)

What can I do to fix this?

Many thanks.

[Zubair_Muhammad]

HI @ianwuk

Use the DIVIDE function

Refund % =
DIVIDE (
    AffiliatesMarch2018[Total Refunds Count],
    AffiliatesMarch2018[Total Sales Count]
)

[Zubair_Muhammad]

@ianwuk

Also there is no need to multiply my 100.

From the Modelling Tab... you can select the formatting of MEASURE as percentage

[ianwuk]

Thanks again @Zubair_Muhammad for all your help!

One more question please, if I may?

Right now, this PowerBi reads from a CSV file called 'March2018.csv' and all formulas and measures in Power Bi are based on that.

How can I make it so that, for next month, when I get a new CSV file called 'April2018.csv' with the same data, just for April, I can just load that new csv in to Power Bi and all the measures and formulas and such will use this new CSV file and I do not have to change everything manually in Power Bi?

Many thanks for your help.