summary stats

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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.