DAX Formula

[BraunReivn]

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.

Table 1

Code	Date	Stage	HC	Code_Stage (calculated column)	Stage_HC (calculated column)
X4567	01/14/2023	C	14	X4567C	14
X4567	12/10/2022	B	15	X4567B	15
X4567	12/05/2022	B	17	X4567B	15
X4567	10/22/2022	A	20	X4567A	20

Sample table; there's a long list of data.

I need to get the latest HC per Code and Stage.

In the table above, X4567B has 2 entries -- 15 HC on 12/10/2022 and 17 HC on 12/05/2022.

In this case, X4567B would take 15 as its HC.

To get the latest HC, I created a calculated column - Stage_HC

Stage_HC =

CALCULATE(

     SUM('Table 1'[HC]),

     FILTER('Table 1','Table 1'[Date]

     =CALCULATE(

     MAX('Table 1'[Date]),

     FILTER('Table 1','Table 1'[Code_Stage]=EARLIER('Table 1'[Code_Stage])))

     && 'Table 1'[Code_Stage]=EARLIER('Table 1'[Code_Stage])))

Table 2 (related to Table 1 by Code_Stage)

Respondent ID	Date	Code	Stage	Code_Stage (calculated column)
114178000	11/17/2022	x4567	A	x4567A
114178001	11/17/2022	x4567	A	x4567A
114178002	11/17/2022	x4567	A	x4567A
114178003	11/17/2022	x4567	A	x4567A
114178004	11/17/2022	x4567	A	x4567A
114178005	12/10/2022	x4567	B	x4567B
114178006	12/10/2022	x4567	B	x4567B
114178007	12/10/2022	x4567	B	x4567B
114178008	12/10/2022	x4567	B	x4567B
114178009	12/10/2022	x4567	B	x4567B
114178010	12/25/2022	x4567	B	x4567B
114178011	12/25/2022	x4567	B	x4567B
114178012	12/25/2022	x4567	B	x4567B
114178013	12/25/2022	x4567	B	x4567B
114178014	12/25/2022	x4567	B	x4567B
114178015	12/25/2022	x4567	B	x4567B
114178016	12/25/2022	x4567	B	x4567B
114178017	12/25/2022	x4567	B	x4567B
114178018	12/25/2022	x4567	B	x4567B
114178019	12/25/2022	x4567	B	x4567B

The desired output should be like this.

Table Visual

Respondent ID	Date	Code_Stage	Stage	# of Respondent (calculated measure)	Stage_HC	Take Rate (calculated measure)
114178000	11/17/2022	x4567A	A	5	20	25%
114178001	11/17/2022	x4567A	A	5	20	25%
114178002	11/17/2022	x4567A	A	5	20	25%
114178003	11/17/2022	x4567A	A	5	20	25%
114178004	11/17/2022	x4567A	A	5	20	25%
114178005	12/10/2022	x4567B	B	5	15	33%
114178006	12/10/2022	x4567B	B	5	15	33%
114178007	12/10/2022	x4567B	B	5	15	33%
114178008	12/10/2022	x4567B	B	5	15	33%
114178009	12/10/2022	x4567B	B	5	15	33%
114178010	12/25/2022	x4567B	B	10	15	67%
114178011	12/25/2022	x4567B	B	10	15	67%
114178012	12/25/2022	x4567B	B	10	15	67%
114178013	12/25/2022	x4567B	B	10	15	67%
114178014	12/25/2022	x4567B	B	10	15	67%
114178015	12/25/2022	x4567B	B	10	15	67%
114178016	12/25/2022	x4567B	B	10	15	67%
114178017	12/25/2022	x4567B	B	10	15	67%
114178018	12/25/2022	x4567B	B	10	15	67%
114178019	12/25/2022	x4567B	B	10	15	67%

Card Visual (Overall take rate)

where:

# of Respondent = count of respondents based on date, code, stage

Take Rate = # of Respondent divide by Stage_HC

Calculated measure:

# of Respondent =

CALCULATE(

     COUNTROWS('Table 2'),

     FILTER('Table 2','Table 2'[Stage]='Table 2'[Stage] &&

     'Table 2'[Date]='Table 2'[Date]))

Take Rate =

DIVIDE(COUNT('Table 2'[# of Respondent]),SELECTEDVALUE('Table 1',[Stage_HC]))

These measures managed to get me the desired Table visual output but not the Card visual which returns (Blank).

I hope I was able to explain it clearly, and not violating any forum rules. 

Thanks in advance.

BraunReivn

[v-zhangti · Accepted solution]

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.

[v-zhangti · Accepted solution]

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.

[BraunReivn]

Thank you so much for noticing and posting a reply. I'm sure this one will work as well.

[lbendlin]

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]

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]

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]

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.

[lbendlin]

If you look at my proposal you see that I am using AVERAGEX.  Can you confirm that you use the same?

[BraunReivn]

Yes, I did. 

[lbendlin]

would you be able to post the sample pbix somewhere?

[lbendlin · Accepted solution]

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.

[BraunReivn]

Thank you so much for all your help.  You're amazing!😊