Forum Discussion
Solved
TopN First Value based on Count in another table
Hello, I am kind of stuck in something that I feel is easy to solve and I need your help (once again). I got two tables connected with a relationship 1 to many like the below: Table 1 ID ...
Managed at least to find how to syntax the formula based on the answer amitchandak gave me.
Top Reason Card = Calculate all the available ID's in table1 Var NumberofID = DISTINCTCOUNT(table1[ID]) // return the reason with the biggest count of ID's (first ask) Var TopReason = CALCULATE( MAX(table2[Reason Name]), topn(1, ALLSELECTED(table2[Reason Name]), calculate(COUNT(table1[ID])), DESC), VALUES(table2[Reason Name]) ) // calculate the count of ID's under the reason with the biggest count (ask 2) Var CalcReference = CALCULATE(DISTINCTCOUNT(table1[ID]), table2[Reason Name] = TopReason) // calculate the percentage in comparison to the total population (ask 3) Var PercReference = FORMAT(CalcReference/NumberofID,"##%") Return Var TopReason & " - " & CalcReference & " / " & CalcReference // that will return Name 2 - 2 / 50%
Managed at least to find how to syntax the formula based on the answer amitchandak gave me.
Top Reason Card =
Calculate all the available ID's in table1
Var NumberofID =
DISTINCTCOUNT(table1[ID])
// return the reason with the biggest count of ID's (first ask)
Var TopReason =
CALCULATE(
MAX(table2[Reason Name]), topn(1, ALLSELECTED(table2[Reason Name]), calculate(COUNT(table1[ID])), DESC), VALUES(table2[Reason Name])
)
// calculate the count of ID's under the reason with the biggest count (ask 2)
Var CalcReference = CALCULATE(DISTINCTCOUNT(table1[ID]), table2[Reason Name] = TopReason)
// calculate the percentage in comparison to the total population (ask 3)
Var PercReference = FORMAT(CalcReference/NumberofID,"##%")
Return
Var TopReason & " - " & CalcReference & " / " & CalcReference
// that will return
Name 2 - 2 / 50%