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;

 

  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.

 

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

6 Replies

  • 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