Summary Statistics of Sales

[jbskarb]

I'm relatively new to Power BI and could use some help generating some summary statistics.

Here's a summary of the columns in the table I'm working with:

Customer ID	Invoice Date	Invoice Amount
123456	1/1/2019	$36.77
123456	1/8/2019	$52.19
321654	1/2/2019	$257.15
321654	1/12/2019	$175.99

I'd like to calculate the average (mean) invoice amount per customer (e.g. $44.88 for customer 123456), and then calculate the percentage of cusomters with average (mean) invoice amounts within various dollar ranges.  For example:

Average Invoice	Percent of all Invoices
$1 - $49.99	50%
$50 - $99.99	0%
$100 - $199.99	0%
$200+	50%

I don't necessarily need a separate summary table for the data; I just need to be able to display the results in a pie chart on a report that filters based on the Invoice Date range (Invoice Date is already connected to a sepate calendar table).

Thank you in advance for the help!

Justin

[v-yulgu-msft · Accepted solution]

Hi @jbskarb,

First, you should add two columns to separately show the Min value and Max value of one type of average range, like below. To achieve this, please refer to the Power Query.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data 2.xlsx"), null, true),
    #"Average Invoice_Sheet" = Source{[Item="Average Invoice",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Average Invoice_Sheet",{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Average Invoice", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Average Invoice", "Average Invoice - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Average Invoice - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Average Invoice - Copy.1", "Average Invoice - Copy.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Average Invoice - Copy.1", type text}, {"Average Invoice - Copy.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Average Invoice - Copy.1", "Min"}, {"Average Invoice - Copy.2", "Max"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","+","",Replacer.ReplaceText,{"Min"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","$","",Replacer.ReplaceText,{"Min"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","$","",Replacer.ReplaceText,{"Max"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value2",{{"Min", type number}, {"Max", type number}})
in
    #"Changed Type3"

Then, in report view, create below measures.

average per customer = 
CALCULATE (
    AVERAGE ( 'Sample Table'[Invoice Amount] ),
    FILTER (
        ALLSELECTED ( 'Sample Table' ),
        'Sample Table'[Customer ID] = SELECTEDVALUE ( 'Sample Table'[Customer ID] )
    )
)

count customer = 
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Sample Table'[Customer ID] ),
        FILTER (
            ALLSELECTED ( 'Sample Table' ),
            [average per customer] >= SELECTEDVALUE ( 'Average Invoice'[Min] )
                && OR (
                    SELECTEDVALUE ( 'Average Invoice'[Max] ) = BLANK (),
                    [average per customer] <= SELECTEDVALUE ( 'Average Invoice'[Max] )
                )
        )
    ),
    DISTINCTCOUNT ( 'Sample Table'[Customer ID] )
)

Best regards,

Yuliana Gu