Forum Discussion
Simple GP display on Matrix
Hi Guru,
I have tried many different ways trying to build a simple PL with Gross Profit on a matrix but no success. I tried using SWITCH measure to nominate the measures displaying on the matrix but could only display a single level total and fail on the aggregated level.
Could someone please help and let me know what I did wrong?
Thanks in advance,
Ray
Hi clubspec ,
You could try below measure to get minus value.
group mins = if(HASONEVALUE('Table'[GroupB]),CALCULATE(SUM(GL[Amount]), FILTER(GL,GL[GL]=RELATED('Table'[GL]))), CALCULATE(SUM(GL[Amount]), FILTER(GL,GL[GL]=RELATED('Table'[GL])&& RELATED('Table'[GroupB])="R"))-CALCULATE(SUM(GL[Amount]), FILTER(GL,GL[GL]=RELATED('Table'[GL])&& RELATED('Table'[GroupB])="E")))Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
clubspec , I think this M-M join causing it. This should have solved the purpose but not
Display = sumx(SUMMARIZE(Map,Map[group],Map[Desp],"_1",SWITCH(VALUES(Map[Group]),1,[Amt],2,[Rev],3,[Exp],4,[Rev]-[Exp])),[_1])Create a bridge table and try
https://www.seerinteractive.com/blog/join-many-many-power-bi/
Hi amitchandak ,
Tried your measure but no luck. Is my "Map" table set up correctly for the purpose? If not how would you change it?
Thanks,
Ray
Hi clubspec ,
I am not sure whether you could change your Map table, I change your Map Table(delete total rows), then create relationship between GL and it. Then you could use Matrix to achieve this goal. You could refer to my sample for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi dax Zoe,
That is not right because the GP is Total Rev minus Total Exp. What you did is to sum them all.
And the Description (Desp) must exactly per the map, that's why I used measures to calculate the Total Rev, Total Exp, and GP. And then use SWITCH to nominate which measures I wanna show.
Hi clubspec ,
You could try below measure to get minus value.
group mins = if(HASONEVALUE('Table'[GroupB]),CALCULATE(SUM(GL[Amount]), FILTER(GL,GL[GL]=RELATED('Table'[GL]))), CALCULATE(SUM(GL[Amount]), FILTER(GL,GL[GL]=RELATED('Table'[GL])&& RELATED('Table'[GroupB])="R"))-CALCULATE(SUM(GL[Amount]), FILTER(GL,GL[GL]=RELATED('Table'[GL])&& RELATED('Table'[GroupB])="E")))Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
