Forum Discussion

RedOcean's avatar
RedOcean
Frequent Visitor
9 years ago
Solved

Creating matrix from two tables and a calculation

Hello,   I have two tables with these fields;   Community (of agents) Agent ID Country Rank (1 to 10) Referal Channel Joining Date Referal Spend Referal Channel  Total Spend   I w...
Need Help
  • RedOcean's avatar
    RedOcean
    9 years ago

    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's avatar
    v-ljerr-msft
    9 years ago

    Hi RedOcean,

     

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

    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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi RedOcean,

 

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

 

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's avatar
RedOcean
Frequent Visitor
9 years ago

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's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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. :smileyhappy:

     

    Regards

    • RedOcean's avatar
      RedOcean
      Frequent Visitor
      9 years ago

      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's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi RedOcean,

         

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

        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