Simple GP display on Matrix

[clubspec]

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?

Excel Data

PBI File

Thanks in advance,

Ray

[dax]

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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[clubspec]

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.

[dax · Accepted solution]

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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@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/

[clubspec]

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