Forum Discussion

BraunReivn's avatar
BraunReivn
Regular Visitor
3 years ago
Solved

DAX Formula

Good day! Please pardon me, I'm new in this community and a complete newbie in PBI. I'll try to be clear and detailed as possible. So, here's my problem. I have 2 tables - Table 1 and Table 2. Ta...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, BraunReivn 

     

    You can try the following methods.
    Measure:

    # of Respondent = CALCULATE(COUNT('Table 2'[Respondent ID]),ALLEXCEPT('Table 2','Table 2'[Date],'Table 2'[Code_Stage]))
    Satge_HC M = CALCULATE(MAX('Table 1'[Stage_HC]),ALLEXCEPT('Table 2','Table 2'[Code_Stage]))
    Take Rate = 
Var _N1=SUMX('Table 2',[# of Respondent])
Var _N2=SUMX('Table 2',[Satge_HC M])
Return
DIVIDE(_N1,_N2)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • lbendlin's avatar
    lbendlin
    3 years ago

    If you change the grouping in your visual you also need to change the formula in the measure.

     

     

    Take Rate = 
var a = SUMMARIZE('Table 2','Table 2'[Date],'Table 2'[Stage],'Table 2'[Code],"tr",DIVIDE([# of Respondent],[Max HC],0))
return AVERAGEX(a,[tr])

    see attached.

     

     

lbendlin's avatar
lbendlin
Super User
3 years ago

1. Please note that Power Query is case sensitive but Power BI is not.  Your Code column values are inconsistent

2.  For the latest HC per Code and Stage you can use REMOVEFILTERS or ALL. You will need to decide if this should be a measure (can be influenced by filters)  or a calculated column (immutable).  Are you planning to allow your users to filter by date ranges?

 

A calculated column could look like this:

Latest HC = 
var st = [Stage]
var cd = [Code]
var md = CALCULATE(max([Date]),All('Table 1'),'Table 1'[Code]=cd,'Table 1'[Stage]=st)
return CALCULATE(max([HC]),All('Table 1'),'Table 1'[Date]=md,'Table 1'[Code]=cd,'Table 1'[Stage]=st)

3. You could create composite keys Stage_Code  but you can also use TREATAS to transfer filters from one table to another.

Max HC = CALCULATE(max('Table 1'[Latest HC]),TREATAS(SUMMARIZE('Table 2','Table 2'[Stage],'Table 2'[Code]),'Table 1'[Stage],'Table 1'[Code]))

4. Your take rate computation is not clear - here is a standard approach to average the value across respondents so that the result can be used both for the rows and for the totals:

Take Rate = 
var a = summarize('Table 2','Table 2'[Respondent ID],"tr",divide([# resp],[Max HC],0))
return AVERAGEX(a,[tr])

 see attached.

  • BraunReivn's avatar
    BraunReivn
    Regular Visitor
    3 years ago

    Thank you lbendlin  for noticing my post and taking the time to answer.

    So I followed everything however it's not giving me the correct overall take rate.  I wonder what's missing here.

    I'd like so much to attach my file however I can't seem to find the option to do it so I'm pasting a screenshot instead.

    Hoping once again for your help.

     

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      Change the implicit measure for the Take Rate column to Average rather than Sum.

       

      You will need to decide if this should be a measure (can be influenced by filters)  or a calculated column (immutable).  Are you planning to allow your users to filter by date ranges?

      • BraunReivn's avatar
        BraunReivn
        Regular Visitor
        3 years ago

        Thank you once again lbendlin.

         

        To answer your question, yes the users may want to filter by date ranges.

         

        As for changing the Take Rate to Average, is there a way to do it in a measure? Take Rate is a measure, not a calculated column.

         

        My apologies if this is taking longer than it should. Your help is greatly appreciated.