Multiple criteria for each line =- PnL

[Anonymous]

Hello,

I am trying to build PnL in power BI and I need to find a way to have different criteria for each line level but they have the same GL account number. for example:

PnL Template

PAPER	need to Sum main account 50000 with  Item group paper Lab or Paper ENVS
WINDOW MATERIAL	need to Sum main account 50000 with  Item group Ext Work
BOXES	need to Sum main account 50000 with  Item group Boxes

MainAccount	Department	cost center	ItemGroup
50000	10		PAPER LAB
50000	10		LABEL
50000	10		INK ENVS
50000	10		EXT WORK
50000	10	LABEL	INK LAB
50000	10	ENVS	BOXES
50000	10	ENVS	PAPER ENVS

Thank you

[Fowmy]

@Anonymous 

The right way to model this is to have a separate dimension table with all the unique values of the Item Groups and their relevant main group. Then you can link your Item Group in the transaction table to this dimension table using Item Group. You can also replace it with a number like ItemGroup ID.

You have the option of creating a relationship or use a function like LOOKUPVALUE to get the correct group.

ItemGroup ID	ItemGroup	Main Group
1	PAPER LAB	Paper
2	LABEL
3	INK ENVS
4	EXT WORK	Window Material
5	INK LAB
6	BOXES	Boxes
7	PAPER ENVS	Paper

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn 

[Anonymous]

@Fowmy 

It does not work because some of the item groups are repeated in different Account number for instance:

GL account                             Item Group         Main Group

50000

PAPER LAB

Paper

60080

PAPER LAB

Paper

I need to find a way and have  the sum for the different criteria of the total line GL + Item Group + Main group

[Fowmy]

@Anonymous 

Having duplicates in the many side is fine But you need to have the UNIQUE list on the dimension table 

Share. Sample PBIX file if with clean explanation if you need see how it can be done. 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy 

Here is the link for the data sample

https://1drv.ms/u/s!Asvh0-7ZghBPn347P5s1pzHCNzyg?e=fs2hmJ

you will see in the P&L that my sales - Label amount 64696$ because each line of sales have a different Main Account

However, that's not the same case in the Raw Material, as you can see the Paper, Window Material, boxes & inks have the same amount 154 272$ because its the same GL used for the Raw Materials but each material have a different Cost Center & Item Group.

Each line of Raw material is a different combination for Example PAPER should be:

Raw material      Main account        Cost Center      Item Group               amount

Paper                     50000           -       ENVS         -     PAPER ENVS           1000

Paper                     50000           -                         -     PAPER ENVS            500

Paper                     50000           -       LABEL         -     PAPER LAB              500

Paper                     50000           -       PACKAGIN-     PAPER PKG               1000

-----------------------------------------------------------------------------------------------

In the P&L total Paper                                                                                    3000$