Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi people)
Need a help in BI.
I am going to do a portfolio vintage analysis.
For example, I have got an excel input file with such a structure:
Consideration date | Name | Receiving date | Amount of Agreement (in origin will be a number) | Amount (in origin will be a number) |
March 19 | Tome | February 19 | P1 | M11 |
March 19 | Bob | March 19 | P2 | M21 |
March 19 | Allen | March 19 | P3 | M31 |
March 19 | Mike | March 19 | P4 | M41 |
April 19 | Tome | February 19 | P1 | M12 |
April 19 | Bob | March 19 | P2 | M22 |
April 19 | Mike | March 19 | P4 | M42 |
April 19 | Jim | April 19 | P5 | M51 |
April 19 | Nike | April 19 | P6 | M61 |
May 19 | Tome | February 19 | P1 | M13 |
May 19 | Bob | March 19 | P2 | M23 |
May 19 | Mike | March 19 | P4 | M43 |
May 19 | Jim | April 19 | P5 | M52 |
May 19 | Nike | April 19 | P6 | M62 |
May 19 | Smith | May 19 | P7 | M71 |
I need to have such a BI matrix with calculation:
| Consideration date | ||
Receiving date | March 19 | April 19 | May 19 |
February 19 | =M11/P1 | =M12/P1 | =M13/P1 |
March 19 | =(M21+M31+M41)/(P2+P3+P4) | =(M22+M42)/(P2+P3+P4) | =(M23+M43)/(P2+P3+P4) |
April 19 | - | =(M51+M61)/(P5+P6) | =(M52+M62)/(P5+P6) |
May 19 | - | - | =M71/P7 |
I made something like that, but denominators in the matrix were changing by column to column, but it must by constant.
How can it be done in BI?)
Hi,
Given the data (as seen in the MS Excel screenshot below), this is the result i got (See second screenshot). This is the measure i wrote
Measure = SUM(Data[Amount])/SUM(Data[Amount of Agreement])
Our denominator must be constant. As us see, Allen received amount in March 19, but he didn't have it in April 19 and so on. That's why your denominator decrease:
We are doing portfolio analysis, not by client.
The target solution in Power BI, which I can make by excel, is this:
Hi,
I am not sure of how robut this solution is but try it out. You may download my PBI file from here.
Hope this helps.
Dear @Ashish_Mathur
Yes it works, but it is a bulky method 😕
I think, there can be a easier one.
Even if we will have such a table structure for denominator, it will work:
This limitation will help: denominator we need is MAX for Receiving date, and it is first coming Consideration date.
Do it Yourself now.
I can do that in Excel, I am not so familiar with DAX.
In Excel I will count Max of each rows:
divide each column of numerator to denominator MAX column:
and will have the target solution:
Hi @MCstudio ,
You can create column in DAX below.
Column1 = DIVIDE(CALCULATE(SUM(Table1[Amount]),FILTER(Table1,Table1[Receiving date]=EARLIER(Table1[Receiving date])&&Table1[Consideration date]=EARLIER(Table1[Consideration date]))), CALCULATE(SUM(Table1[Amount of Agreement]),FILTER(Table1,Table1[Consideration date]=EARLIER(Table1[Consideration date]))))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xicai ,
I have checked your DAX formula and numerator - denominator separately:
Hi @MCstudio ,
What about DAX below? The EARLIER function here is used to categorize the date column.
Column1 = DIVIDE(CALCULATE(SUM(Table1[Amount]),FILTER(Table1,Table1[Receiving date]=EARLIER(Table1[Receiving date])&&Table1[Consideration date]=EARLIER(Table1[Consideration date]))), CALCULATE(SUM(Table1[Amount of Agreement]),FILTER(Table1,Table1[Receiving date]=EARLIER(Table1[Receiving date]))))
Best Regards,
Amy
Dear @v-xicai
as I said, something is wrong with denominator.
If we use this numbers:
Consideration date | Name | Receiving date | Amount of Agreement | Amount |
Mar-19 | Tome | Feb-19 | 1 | 11 |
Mar-19 | Bob | Mar-19 | 2 | 21 |
Mar-19 | Allen | Mar-19 | 3 | 31 |
Mar-19 | Mike | Mar-19 | 4 | 41 |
Apr-19 | Tome | Feb-19 | 1 | 12 |
Apr-19 | Bob | Mar-19 | 2 | 22 |
Apr-19 | Mike | Mar-19 | 4 | 42 |
Apr-19 | Jim | Apr-19 | 5 | 51 |
Apr-19 | Nike | Apr-19 | 6 | 61 |
May-19 | Tome | Feb-19 | 1 | 13 |
May-19 | Bob | Mar-19 | 2 | 23 |
May-19 | Mike | Mar-19 | 4 | 43 |
May-19 | Jim | Apr-19 | 5 | 52 |
May-19 | Nike | Apr-19 | 6 | 62 |
May-19 | Smith | May-19 | 7 | 71 |
With your formula, we will have:
But our target result is:
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |