Forum Discussion

jnrezk's avatar
jnrezk
Icon for Helper III rankHelper III
5 years ago
Solved

formatting & merging 3 tables in matrix view

Hi - I have 3 tables. Two are from SQL server so I don't think I can really alter it much. The other is an excel file. I need to connect the 3. Table 1: SQL server looks like this: Week End Pl...
Need Help
  • nandic's avatar
    nandic
    5 years ago

    Hi jnrezk ,

     

    Did you try joining it in Power Query?
    If there are 3 tables:
    1) sql plans
    2) sql quotes
    3) excel - targets for plans and quotes
    They all have week information. Use this week field to merge tables (as new query if needed) and you will have all fields in one table.
    Start from table which will always have data.
    Example: select Excel table, merge queries as new, merge with Plans table.
    Then inside that new table, go again to merge queries as new, merget with Quotes table.
    Expand needed columns (value, no need for week).

     

     

    Best regards,

    Nemanja Andic

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi jnrezk ,

 

First create a relationship between the tables from sql,like below:

Then create a dim table as below:

With three measures:

Target = 
var _maxdate=MAXX(ALL(SQL1[Week End]),[Week End])
Return
SWITCH(SELECTEDVALUE('Table'[Column1]),
"Plans",CALCULATE(SUM(Excel[Targeted Number of Plans]),FILTER(ALL(Excel),'Excel'[Week End]=_maxdate)),
"Quotes",CALCULATE(SUM(Excel[Targeted number of Quotes]),FILTER(ALL(Excel),'Excel'[Week End]=_maxdate)))
Actual = 
var _maxdate=MAXX(ALL(SQL1[Week End]),[Week End])
Return
SWITCH(SELECTEDVALUE('Table'[Column1]),
"Plans",CALCULATE(SUM('SQL1'[Plan Nbr]),FILTER(ALL('SQL1'),'SQL1'[Week End]=_maxdate)),
"Quotes",CALCULATE(SUM(SQL2[Quote Nbr]),FILTER(ALL(SQL2),'SQL2'[Week End]=_maxdate)))
% change = DIVIDE('Table'[Actual]-'Table'[Target],'Table'[Actual])

If you need calculated columns ,see below:

col_Actual = 
var _maxdate=MAXX(ALL(SQL1[Week End]),[Week End])
Return
SWITCH('Table'[Column1],
"Plans",CALCULATE(SUM('SQL1'[Plan Nbr]),FILTER(ALL('SQL1'),'SQL1'[Week End]=_maxdate)),
"Quotes",CALCULATE(SUM(SQL2[Quote Nbr]),FILTER(ALL(SQL2),'SQL2'[Week End]=_maxdate)))
col_Target = 
var _maxdate=MAXX(ALL(SQL1[Week End]),[Week End])
Return
SWITCH('Table'[Column1],
"Plans",CALCULATE(SUM(Excel[Targeted Number of Plans]),FILTER(ALL(Excel),'Excel'[Week End]=_maxdate)),
"Quotes",CALCULATE(SUM(Excel[Targeted number of Quotes]),FILTER(ALL(Excel),'Excel'[Week End]=_maxdate)))
col_% change = DIVIDE('Table'[col_Actual]-'Table'[col_Target],'Table'[col_Actual])

And you will see:

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

jnrezk's avatar
jnrezk
Icon for Helper III rankHelper III
5 years ago

Thank you so much for your detailed response! I think it will work!! I have 2 follow up questions for now. I wanted a count distinct of the plan number but not sure if it worked this is how I did the actual code:

 

Actual =
var _maxdate=MAXX(ALL(Policies[WEEK_OF_YEAR_END_DATE]),[WEEK_OF_YEAR_END_DATE])
Return
SWITCH(SELECTEDVALUE('Summary'[Metric]),
"Plans",CALCULATE(DISTINCTCOUNT('Policies'[PLAN_NBR]),FILTER(ALL('Policies'), Policies[WEEK_OF_YEAR_END_DATE]=_maxdate)),
"Successful Quotes",CALCULATE(COUNTA(Quotes[Successful Quote Indicator]),FILTER(ALL(Quotes),'Quotes'[WEEK_OF_YEAR_END_DATE]=_maxdate)))
 
2) I also have a month field - how can I adjust the code so I can filter on either?

thank you again!
  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  jnrezk,

     

    For question 1:

    I think it will work.

    For question 2:

    If you also have a month filter,you could modify the variable as below:

     

    var _maxdate=Calculate(MAX([WEEK_OF_YEAR_END_DATE]),FILTER(ALL(Policies),[Month]=SELECTEDVALUE([MONTH])))

     

    If my method works,could you pls mark the reply as answered to let more people find it?

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!