Creating matrix from two tables and a calculation

[RedOcean]

Hello,

I have two tables with these fields;

1. Community (of agents)
   • Agent ID
   • Country
   • Rank (1 to 10)
   • Referal Channel
   • Joining Date
2. Referal Spend
   • Referal Channel 
   • Total Spend

I want to create a matrix chart  in POWER BI that looks like the excel pivot table below that shows me by month the average cost/spend per agent WHERE Agents have a Rank of >1. 

Thank you.

[RedOcean · Accepted solution]

I am really grateful for your help.

Below is a link.

there are 3 tables, linked.

I have shown a single with 2 table visualisations on sheet "Recruit$ L2+" 

They show 1) the total number of new agents, by month/country/recruitment-channel that have an RPLevel>1

2) The total $ spend on each recruitment-channel by month/country

What I require is a new table that shows me average cost recruitment cost of agents each month that have RPLevel>1.

For example, in Hong Kong in 2017-7 we spent $137.34 and have 36 agents recruited in that month that are RPLevel>1. So the average should be $3.82

Many thanks for your help!

https://www.dropbox.com/sh/kloegc6bsenuxd3/AADaBUOrZ90b63guu7WA73BEa?dl=0

[v-ljerr-msft]

Hi @RedOcean,

According to your description above, you should be able to follow steps below to create the Matrix visual with Power BI. 

1. Use the formula below to create a new measure to calculate the average of spend. Note: make sure you have created a relationship between the two table with 'Referal Channel' column.

Average of Spend =
CALCULATE (
    AVERAGE ( 'Referal Spend'[Total Spend] ),
    FILTER ( 'Community (of agents)', 'Community (of agents)'[Rank] > 1 )
)

2. Add a Year-Month calculate column in your 'Community (of agents)' table if you don't have it yet.

YearMonth =
YEAR ( 'Community (of agents)'[Joining Date] ) & "-"
    & MONTH ( 'Community (of agents)'[Joining Date] )

3. Then you should be able to add a Matrix visual on the report, with 'County' and 'Referal Channel' column as Rows, 'YearMonth' column as Columns, and [Average of Spend] measure as Values. 

Regards

[RedOcean]

Thank you for your help and I think I understand the logic. However, there are multiple records of Spend on a single channel in each month. I thus need to also summarise the spend per channel per month in the formula and I am not sure how to do that. Any clues?

Many thanks 

[v-ljerr-msft]

Hi @RedOcean,

Could you post some sample/mock data with your expected result here, so that I can further assist on the issue? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[RedOcean · Accepted solution]

I am really grateful for your help.

Below is a link.

there are 3 tables, linked.

I have shown a single with 2 table visualisations on sheet "Recruit$ L2+" 

They show 1) the total number of new agents, by month/country/recruitment-channel that have an RPLevel>1

2) The total $ spend on each recruitment-channel by month/country

What I require is a new table that shows me average cost recruitment cost of agents each month that have RPLevel>1.

For example, in Hong Kong in 2017-7 we spent $137.34 and have 36 agents recruited in that month that are RPLevel>1. So the average should be $3.82

Many thanks for your help!

https://www.dropbox.com/sh/kloegc6bsenuxd3/AADaBUOrZ90b63guu7WA73BEa?dl=0

[v-ljerr-msft · Accepted solution]

Hi @RedOcean,

Based on my test, the formula below should work in your scenario. 

Measure =
VAR currentCountry =
    FIRSTNONBLANK ( 'Community Recruitment Spend'[Country], 1 )
VAR currentReferalSourceID =
    MAX ( 'Community Recruitment Spend'[Referal Source ID] )
VAR currentMonth =
    MONTH ( MAX ( 'Community Recruitment Spend'[Month] ) )
VAR currentYear =
    YEAR ( MAX ( 'Community Recruitment Spend'[Month] ) )
RETURN
    DIVIDE (
        (
            DIVIDE (
                SUM ( 'Community Recruitment Spend'[Spend (USD)] ),
                CALCULATE (
                    COUNTA ( CommunityView[AgentID] ),
                    FILTER (
                        ALL ( CommunityView ),
                        CommunityView[CountryName] = currentCountry
                            && CommunityView[ReferralSourcesId] = currentReferalSourceID
                            && MONTH ( CommunityView[RegistrationDate] ) = currentMonth
                            && YEAR ( CommunityView[RegistrationDate] ) = currentYear
                            && CommunityView[RPLevel] > 1
                    )
                )
            )
        ),
        DISTINCTCOUNT ( 'Community Recruitment Spend'[SpendYearMonth] )
    )

Regards

[RedOcean]

Absolutely brilliant. Very much appreciated.

A question though: there are a number of joining agents which are not as a result of any Recruitment Spend. How can we take these into account when calculating the average cost of recruitment.

E.g. In 2017-07 in Hong Kong, in total, there are 140 agents and a spend of $193.91 which makes the overall average spend as $1.39

Currently these agents are not considered in the calulcation on so the overall average in July in HK is $6.46

Can this be done?