Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi guys,
i've got a matrix visualization that calculates the sum of standing loans and approved loans by "fund".
it looks something like this and uses a very simple sum measure.
fund c is acutally a co investment by fund a and fund b so i'd like to refine the measure so that the output would ignore fund c and add 50% of it's balance to fund a and fund b.
the result should look like this :
can anyone think of a method to achieve this output?
thanks!!!!.
Hi @orihait , I would create the next calculated columns:
fund ab = var VarValue=Table1[status] return (LOOKUPVALUE(Table1[fund c];Table1[status];VarValue)/2)+Table1[fund a]
fund bb = var VarValue=Table1[status] return (LOOKUPVALUE(Table1[fund c];Table1[status];VarValue)/2)+Table1[fund b]
You would get your data:
Best Regards,
Miguel
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi and thanks again for taking the time to help.
is there any way i could do it without creating new collumns i.e by using the matrix's filter context?
the raw data looks something like this
and should look like this on the matrix :
i figured i could use the visualization filter to edit fund c out and show only the ones i want.
thanks again! i highly appreciate it!
As far I know, you can create the matrix but not calculated like you need (fund a=fund a+ fund c/2 and fund b=fund b + fund c/2.
You would need calculate to achieve this.
Hi @orihait ,
Don't know if this answer is still on time but try the following:
Fund Parent Percentage
C | A | 50% |
C | B | 50% |
A | A | 100% |
B | B | 100% |
D | D | 100% |
Measure =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( Participation; Participation[Parent] );
"@Total"; CALCULATE (
SUM ( Projects[Total] );
FILTER ( Projects; Projects[Fund] = Participation[Parent] )
)
);
[@Total]
)
+ SUMX (
ADDCOLUMNS (
SUMMARIZE ( Participation; Participation[Fund]; Participation[Percentage] );
"@Total"; CALCULATE (
SUM ( Projects[Total] ) * SELECTEDVALUE ( Participation[Percentage] );
FILTER (
Projects;
Projects[Fund] = Participation[Fund]
&& Participation[Percentage] <> 1
)
)
);
[@Total]
)
Now create your matrix with the Parent column on the previous created table as columns, status on rows and the measure on values.
should give expected result.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |