Trying to get Dynamic Weighted Average

[rvokkarane]

If you look at the picture, the column ww is where i'm trying to get the weighted average. The challenge so far has been to get the Loan to group by Score Group and be dynamic to date slicer. Basically I need it to say in the month of *whatever*, what is the W,avg % for the group 150-159. 

So far I have managed to group the loan $ for the entire data pool but it does not slice to the date. 

Please help. I'm banging my heads against a wall trying to get this to work. 

[v-yuezhe-msft · Accepted solution]

---

@rvokkarane wrote:

Lydia,

  The final goal is to get to % value. For example: between given two dates, SUM of LOAN of 150-159/SUM of all Loans. Again between two dates, not all time. 

---

Hi @rvokkarane,

Create the following measures, check if percent returns your expected result.

Sum of loan of score group between dates = CALCULATE(SUM(Query1[LOAN]),ALLSELECTED(Query1[Date]))
Sum of all loan between dates = CALCULATE(SUM(Query1[LOAN]),ALLSELECTED(Query1[Score Group]))
percent = [Sum of loan of score group between dates]/[Sum of all loan between dates]

You can see the example.

[rvokkarane]

[v-yuezhe-msft]

Hi @rvokkarane,

Based on your description, the ww column doesn’t filter by DATESOLD slicer and the Query1 contains DATESOLD column , right? If that is the case, change your formula as follows.

Column = CALCULATE(SUM(Query1[LOAN]),FILTER(ALLSELECTED(Query1[DATESOLD]),Query1[Score Group]=EARLIER(Query1[Score Group])))

However, if the above formula doesn’t help, please share raw data of your table and post expected result here.

Thanks,
Lydia Zhang

[rvokkarane]

Lydia, 

I have also attached a expected result here. So the Sold & Originations column are dynamic with datesold and are grouped by Score group mentioned earlier. 

I think another challenge would be to get the denominator to work, because I want it to use the Total for the data being displayed or dynamic to datesold, in this case the 7million which for Sept-16. I appreciate your help so much. Thank you. 

[rvokkarane]

Lydia,

Thank you for replying. Anyway the formula did not work, it gets grouped individually. I have attached the raw data, please take a look. I'm trying to group the contracts by Score group as well as work with datesold. The final goal is to get to % value. For example: between given two dates, SUM of LOAN of 150-159/SUM of all Loans. Again between two dates, not all time. 

[v-yuezhe-msft · Accepted solution]

---

@rvokkarane wrote:

Lydia,

  The final goal is to get to % value. For example: between given two dates, SUM of LOAN of 150-159/SUM of all Loans. Again between two dates, not all time. 

---

Hi @rvokkarane,

Create the following measures, check if percent returns your expected result.

Sum of loan of score group between dates = CALCULATE(SUM(Query1[LOAN]),ALLSELECTED(Query1[Date]))
Sum of all loan between dates = CALCULATE(SUM(Query1[LOAN]),ALLSELECTED(Query1[Score Group]))
percent = [Sum of loan of score group between dates]/[Sum of all loan between dates]

You can see the example.

[rvokkarane]

Lydia,

Thank you so much for helping me, it worked. 🙂

But I did run into a problem. If you look at the picture below, the column ww which sum of score group by dates, some groups are totalling wrong. In this example, the group 160-169 and 170-179 and so on, the loan column and ww should be the same but it doesn't seem to be.

Thank you so much again.

[v-yuezhe-msft · Accepted solution]

Hi @rvokkarane,

Please ensure that you create a  measure named ww as shown in the following screenshot? If the issue still persists, please share me your PBIX file so that I can test.


Thanks,
Lydia Zhang

[rvokkarane]

Lydia,

It worked. Thank you so much.

While I was trying to solve this problem. I think I came across a easier way to solve this problem.

For example: The loan column from my query, just use the same column again and do a quick calculation, then do a sum and percent of grand total and gives us the dynamic weighted avg %.

I did use your formula to calculate a different calculation.

Again thank you so much 🙂

I appreciate it so much.