Calculate total of subset based on slicer

[MSC]

Hi everyone!

I'm working on a customer analysis dashboard and have some problems creating a benchmark table. Even though I've worked with PBI for some time now, I'm new to DAX functions. I've tried to find a solution (or at least an approach for my problem) in the various questions about subtotals etc. but as I wasn't able to find something useful, I'd like to give you an overview of what I'd like to do.

Any suggestions or hints will be extremely helpful for me.

Problem at hand:

I'd like to create a benchmark table for the last two years (e.g. 2019 and 2018) in which I can compare a customer against the customer group she belongs to. This comparison should be done in two dimensions (e.g. age and salary).

However, since the dashboard is primarily used to analyse one specific customer at a time, there's a customer slicer on top of my report page and the benchmark table should be based on this dropdown slicer.

So, for example, when I select customer B with the slicer, the benchmark table "knows" that B belongs to customer group "Professionals" calculating the average age and salary of this group.

Please let me know, if you need further information.

Thanks in advance!

[MSC]

@AllisonKennedy  @AlB 

Thank you both for your responses and the suggested functions! It's really nice to see that there are other users willing to support when one needs help!

[AlB]

@MSC 

Your explanation is rather confusing. I don't see any 25% or any "Percentage" column. Moreover, the [PercentageTotalGroup] measure already seems to yield the result you are looking for, so I do not understand your request to use it in the code I provided earlier. The following is an alternative to calculate that percentage using the pattern I suggested in the previous code. Note it uses only one column of your table in the sum (Total purchases/customer). I do not think the column "Total purchases/customer group" is necessary

Percentage of purchases in group =
VARcustGrpp_ = DISTINCT ( 'DataTable'[Customer Group] )
VARpurchasesCurrentCustomerr_ = SUM ( 'DataTable'[Total purchases/customer] )
VARpurchasesInGroupp_ =
    CALCULATE (
        SUM ( 'DataTable'[Total purchases/customer] ),
        'DataTable'[Customer Group] INcustGrpp_,
        ALL ( 'DataTable'[Customer] )
    )
RETURN
    DIVIDE (purchasesCurrentCustomerr_,purchasesInGroupp_ )

I would need to see the code for another one of those measures to try and make more sense of this

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@MSC 

I don't understand what you're after. Best would be to provide an example with the expected result based on the data in the pbix you've shared

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MSC]

Ok, I've just added two new files.

I want to compare the individual share of spending against the average share of spending of the customer group. I.e. in the dashboard you can see that customer B's purchases represented 3% of the customer group's total sales. However, in 2018, the average customer's spending in B's group was much higher, namely 25%. Now, I want your DAX function to return this 25% (in case of I select B).

In the meantime, as you can see, I've figured out that I can create a new column ("Percentage") in my underlying data table and insert this column in your function. This works.

But how can I use the measure "PercentageTotalGroup" I created instead of "Percentage" to return the 25%?

The problem is that a colleague of mine has already created a lot of measures which I'd like to use in combination with your function.

[MSC]

@AlBThe excel file "calculation" is just for illustration purposes and for showing what I mean with "average spending".

[AlB · Accepted solution]

@MSC 

Average age in group = 
VAR custGrp_ =
    DISTINCT('DataTable'[Customer Group])
RETURN
    CALCULATE(AVERAGE('DataTable'[Age]), 'DataTable'[Customer Group] IN custGrp_, ALL( 'DataTable'[Customer]) )

Average salary in group = 
VAR custGrp_ =
    DISTINCT('DataTable'[Customer Group])
RETURN
    CALCULATE(AVERAGE('DataTable'[Salary]), 'DataTable'[Customer Group] IN custGrp_, ALL( 'DataTable'[Customer]) )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MSC]

@AlBGreat, thank you!!!

Is there a possibility to do exactly the same but with a measure, i.e. the return the average of a measure?

I've expanded the data on github ( https://github.com/MSC791/PBI-Test.git   ) and uploaded a v2 version to highlight what I'm talking about.

Imagine you know how much a customer spends in comparison to all customers of the same group in a given year. In order to evaluate this number, you would like to compare it to the average customer spending in this group.

I don't understand how I can integrate the measure in the DAX function you provided above.

[MSC]

@AlB @Ashish_Mathur @AllisonKennedy 

I've created a github repository where you can find an illustrative example of the dashboard and the underlying excel file.

https://github.com/MSC791/PBI-Test.git 

@AlBI'm very sorry but as you'll see in the dashboard, your function returns the value of the customer I've selected and not the average of the selected cusomter's group.

[AlB]

@MSC 

so the solution I provided earlier does exactly that:

AvgAge =
VAR custGrp_ =
    DISTINCT ( Table1[CustomerGroup] )
RETURN
    CALCULATE ( AVERAGE ( Table1[Age] ), Table1[CustomerGroup] = custGrp_ )

with Table1[Customer] as slicer. and the same for salary

AvgAge =
VAR custGrp_ =
    DISTINCT ( Table1[CustomerGroup] )
RETURN
    CALCULATE ( AVERAGE ( Table1[Salary] ), Table1[CustomerGroup] = custGrp_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@MSC 

yes. It's vague. You'd need to show a sample of the relevant tables to get a more accurate answer.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MSC]

@AlB  To keep it really simple, just image something like this:

Of course, my actual data table is much more complex but the underlying structure is the same. All data is in one table.

[Ashish_Mathur]

Hi,

What exact result are you expecting on this small sample dataset.

[MSC]

@Ashish_MathurHi! This is just for illustration purposes. My main concern is how to calculate the averages of a customer group based on a filter selecting only one customer.

[MSC]

And as I've stated above, the goal of the benchmark table I'd like to create is to return the averages for age and salary for all customers belonging to the same customer group as B.

[AllisonKennedy]

How is your data model set up? Are age and salary in the same table or do you have a different Dimension table for each? There are many ways to solve this problem, here's one solution (substitute education for age in your example): 

Average Profit Subtotal =

VAR _education = SELECTEDVALUE(DimCustomer[Education])

VAR _salary = SELECTEDVALUE(DimCustomer[YearlyIncome])

RETURN

IF(HASONEVALUE(DimCustomer[CustomerKey]),

CALCULATE(AVERAGE(FactInternetSales[Profit]), DimCustomer[YearlyIncome]=_salary, DimCustomer[Education]=_education),

"Select a customer")

[MSC]

@AllisonKennedyThanks a lot for your solution!

All of my data comes from one very big table.

As I've already replied before, I might have been a little bit vague with respect to my main problem, i.e. how to calculate the average of the benchmark customers. Sorry for that!
Imagine you select customer B and the visuals of the dashboard show that she is 35 years old and earns $65.000 per year.
The goal of the benchmark table I'd like to create is to return the averages for age and salary for all customers belonging to the same customer group as B.
E.g. the selected customer B belongs to customer group "Professionals" and the table then returns 42 (years) and 60.000 (salary) as the averages for this group.

Hence, I think both variables must be numeric (i.e. education won't work here). With respect to the solution you proposed, I don't understand the part with AVERAGE(FactInternetSales[Profit]) but I think this is a result of my vague problem description.

[AllisonKennedy]

@MSC 

I haven't had time to look at your link yet, but sounds like you're only comparing on 1 dimension, and wanting to find average of the others, so you'll need two measures: 

Average Age =

VAR _CustomerGroup= SELECTEDVALUE(Table[Customer Group])

RETURN

IF(HASONEVALUE(Table[Customer]),

CALCULATE(AVERAGE(Table[Age]), Table[Customer Group]=_CustomerGroup),

"Select a customer")

Average Salary=

VAR _CustomerGroup= SELECTEDVALUE(Table[Customer Group])

RETURN

IF(HASONEVALUE(Table[Customer]),

CALCULATE(AVERAGE(Table[Salary]), Table[Customer Group]=_CustomerGroup),

"Select a customer")

[AlB]

Hi @MSC 

You can create two measures. One for the age:

AvgAge =
VAR custGrp_ =
    DISTINCT ( Table1[Group] )
RETURN
    CALCULATE ( AVERAGE ( Table1[Age] ), Table1[Group] = custGrp_ )

and similarly, for the income:

AvgIncome =
VAR custGrp_ =
    DISTINCT ( Table1[Group] )
RETURN
    CALCULATE ( AVERAGE ( Table1[Income] ), Table1[Group] = custGrp_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MSC]

@AlBThanks for your response and your proposed solution. I think, I've missed highlighting the most important point for me... How do I calculate the average of all customer belonging to the same group as the customer selected?

Your solution works perfectly fine but it only returns the value of the company selected.