topic Re: summary stats in Developer

summary stats

Anonymous — Fri, 29 Apr 2022 15:49:21 GMT

I have database of customers and their sales history.

I am comparing the current year sales to the previous year sales

the data would look like this.

customer name 2020 sales $ 2021 sales $ Increase/Decrease $ Increase/Decrease %

joe 100 200 100 100

bob 0 150 New

Sally 250 150 100 -40%

Carol 450 0 Lost

I can do the above fine in powerbi desktop, but what I cannot do is create a summary table like

Type # of Customers

New 45

Increase +200% 7

Increase 150 - 199% 12

Increase 0 - 149% 14

Decrease 32

Lost 45

Re: summary stats

Anonymous — Tue, 03 May 2022 02:03:33 GMT

Hi @Anonymous ,

You can create a Type table first then create a measure to count the number of customer in each type.

Type = DATATABLE( "Type", STRING,{ {"New"}, {"Increase +200%"}, {"Increase 150 - 199%"}, {"Increase 0 - 149%"}, {"Decrease"}, {"Lost"} } )

Measure:

Measure = VAR _ADD = ADDCOLUMNS('Type',"COUNT", SWITCH('Type'[Type], "New",CALCULATE(COUNT('Table'[customer name]),'Table'[Increase/Decrease $] = "New"), "Lost",CALCULATE(COUNT('Table'[customer name]),'Table'[Increase/Decrease $] = "Lost"), "Decrease",CALCULATE(COUNT('Table'[customer name]),'Table'[Increase/Decrease %] < 0), "Increase 0 - 149%",CALCULATE(COUNT('Table'[customer name]),FILTER('Table','Table'[Increase/Decrease %]<>BLANK()&&'Table'[Increase/Decrease %] >= 0&&'Table'[Increase/Decrease %]<=1.49)), "Increase 150 - 199%",CALCULATE(COUNT('Table'[customer name]),FILTER('Table','Table'[Increase/Decrease %] >= 1.5&&'Table'[Increase/Decrease %]<=1.99)), CALCULATE(COUNT('Table'[customer name]),FILTER('Table','Table'[Increase/Decrease %] >= 2)) ) ) RETURN SUMX(_ADD,[COUNT])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: summary stats

Anonymous — Thu, 05 May 2022 14:09:13 GMT

thanks for this code, it looks logical. I was able to update my dashboard to include the table and field name in my system and the 2 column Type and the measure Customer_Stats both save without error. but when I go to use the customer_stats measure and/or the type field I get the error.

query(2,1) a table of multiple values was supplied where a single value was expected.

thoughts

Re: summary stats

Anonymous — Fri, 06 May 2022 06:21:19 GMT

Hi @Anonymous ,

There may be some problem in your code, this error will appear when your code will return multiple values like lists/table but Power BI only support single value.

We can see that my measure above will all return single aggregate values.

If this still couldn't help you solve your problem please share a sample file with me and show me a screenshot with the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.