topic Re: Table visual consisting of many measures with DAX (customized P&L) in DAX Commands and Tips

Table visual consisting of many measures with DAX (customized P&L)

Draszor — Fri, 12 Feb 2021 16:59:25 GMT

Hi,

I need to create a customized P&L. this would be a table (or matrix) visual consisting basically of 2 columns:

- name (like Product sales, services sales, products COGS etc)

- formula calculating the given name value (basically calculate with one or 2 filters)

I was hoping to solve this with table constructor, building something like the below:

Profit & Loss = { ("1","Prod Sales" , calculate(sum(DATA[AMOUNT]), DATA[AG_4_Code] = "PO1120") ), ("2","(Standard prod) Cost of Sales",calculate(sum(DATA[AMOUNT]), DATA[Group Account_Code] = "PL2101312") ), ("3","Write off inventories",calculate(sum(DATA[AMOUNT]), DATA[Group Account_Code] = "PL2102132") ), ("4","Guarantee costs",calculate(sum(DATA[AMOUNT]), DATA[Group Account_Code] = "PL2101422") ), ("5"," ", ), ("6","Prod Gross Margin",calculate(sum(DATA[AMOUNT]), DATA[AG_4_Code] in {"PO1120","PO1220"},not DATA[Group Account_Code] in {"PL4406111","PL4406112"}) ) }

it does not work in a way I want. it shows frozen values so I can not filter such table (when used in visual) by date, division etc.

How to handle such case? I would need severa formulas like the above , unfortunatelly they are a bit unique so one formula for all rows would not work

thx

Re: Table visual consisting of many measures with DAX (customized P&L)

amitchandak — Fri, 12 Feb 2021 17:25:23 GMT

Draszor , There is blog how create financial statement, refer if that can help

https://community.powerbi.com/t5/Desktop/Traditional-Financial-Statements/td-p/7223

Re: Table visual consisting of many measures with DAX (customized P&L)

Draszor — Sun, 14 Feb 2021 14:57:28 GMT

Hi,

I reviewed the attached materials but I need something different.

my GL Accounts are grouped on 5 levels, starting from GL Account, to Group Acc, AccGr1,...AccGr4). the P&L I am trying to build is using one of those aggregates for given row of P&L, for instance, for Total Sales, AccGr4 is used (containing the same value for all sales GL accounts), for warranty costs, group Acc is used etc.

I was thinking to create a unique filter context (with calculate(...,Filter()) for each row of my P&L. that way:

1. I would be able to use different aggregats for different P&L categories

2. I would be able to use different formulas inside calculate(), meaining - sum() for values, and divide() for margins.

I was trying to achieve this task with table manipulators, not being aware that my second row of the table (being a calculate formula, but apparently stored as value in the table) can not be filtered in the report.

if I was to create this in excel, I would use sumifs() with different conditions inside, for different P&L records

Re: Table visual consisting of many measures with DAX (customized P&L)

Draszor — Wed, 17 Feb 2021 20:16:14 GMT

Dear All,

Would Yo suggest any promising way I could investigate to achieve my goal? Detailed solution is not needed, I can work on it, however for the time being I got stuck with generating any promising way

thanka in advance

Re: Table visual consisting of many measures with DAX (customized P&L)

jameszhang0805 — Thu, 18 Feb 2021 12:04:01 GMT

If the screenshot result is not your expectation, please ignore my reply, if yes, please be patient and read through my reply, if you need PBI files I can provide them.

When you created a calculation table with DAX, it can not be filtered by the slicer. Because the table is static, it only can be updated once you refresh the data source. Depending on your requests, you need to do the following, and I'll give you an example.
Suppose you have a fact table just like this:

You want to calculate some measures and logic as below, but these accounts are not in your fact table, you can't filter them directly
Gross Revenue = 400000 - Revenue + 400010 - Revenue Adjustments
Final Discounts = 410000 - Discounts + 410010 - Discounts - Adjustments
Product Cost = 500000 - Cost of Goods Sold + 500010 - Cost of Goods Sold Adjustments
Net Revenue = Gross Revenue + Final Discounts
Product Margin = Net Revenue - Product Cost
PM % = Product Margin / Net Revenue
You want to put all the above measures into your matrix. You should manually create a new table(FinalAccount) just like below and don't need to create a relationship to any other tables.

Then create measure as below :

SumAmount = SUM( 'MF_P&L'[Amount] )

GrossRev = CALCULATE( [SumAmount] , 'Account'[Account Code] in { "400000 - Revenue", "400010 - Revenue Adjustments" } )

FinalDiscounts =

CALCULATE( [SumAmount] , 'Account'[Account Code] in { "410000 - Discounts","410010 - Discounts - Adjustments" } )

ProductCost =

CALCULATE( [SumAmount] , 'Account'[Account Code] in {"500000 - Cost of Goods Sold","500010 - Cost of Goods Sold Adjustments"} )

NetRev = [GrossRev] + [FinalDiscounts]

ProMargin = [NetRev] - [ProductCost]

PM % = DIVIDE( [ProMargin] , [NetRev] )

Finally, create a measure like this:

Put column FinalAccount[Account] and Measure FinalAmount in matrix

Re: Table visual consisting of many measures with DAX (customized P&L)

jameszhang0805 — Sat, 20 Feb 2021 07:20:25 GMT

Hi Draszor :
If the below result is not your expectation, please ignore it.

The following values are not in the fact table, they can not be filtered directly, they are calculated:
Gross Revenue, Final Discount, Product Cost, Net Revenue, Product Margin, PM%
P&L Test.pbix

Re: Table visual consisting of many measures with DAX (customized P&L)

Draszor — Sat, 20 Feb 2021 17:09:33 GMT

Dear jameszhang0805,

this is increadible, I went very similar way to what you propose, not checking my post as I doubted I get any promissing reply.
what I did:

1. I created a new table with index and P&L categories (index is there to make sure the P&L categiries are sorted in my desired order

2. I created a very long matrix, using variables (one variable for one row of my P&L table loaded in previous step)
3. I used switch to assign my variable formula to each P&L index value. I used indexes instead of P&L Categories because it shortens the Swich formula. I think this is the only diff vs your solution, so my swich looks more like:

var _VALUE = SWITCH( SELECTEDVALUE('P&L'[Index] ), 1,if(ISBLANK(_1),0,_1), 2,if(ISBLANK(_2),0,_2), 3,if(ISBLANK(_3),0,_3), 4,if(ISBLANK(_4),0,_4), 5,if(ISBLANK(_5),0,_5), 6,if(ISBLANK(_6),0,_6), 7,if(ISBLANK(_7),0,_7), 8,if(ISBLANK(_8),0,_8), 9,if(ISBLANK(_9),0,_9), 10,if(ISBLANK(_10),0,_10), 11,if(ISBLANK(_11),0,_11), 12,if(ISBLANK(_12),0,_12), 13,if(ISBLANK(_13),0,_13), 14,if(ISBLANK(_14),0,_14), 15,if(ISBLANK(_15),0,_15), 16,if(ISBLANK(_16),0,_16), 17,if(ISBLANK(_17),0,_17), 18,if(ISBLANK(_18),0,_18), 19,if(ISBLANK(_19),0,_19), 20,if(ISBLANK(_20),0,_20), 21,if(ISBLANK(_21),0,_21), 22,if(ISBLANK(_22),0,_22), 23,if(ISBLANK(_23),0,_23), 24,if(ISBLANK(_24),0,_24), 25,if(ISBLANK(_25),0,_25), 26,if(ISBLANK(_26),0,_26), 27,if(ISBLANK(_27),0,_27), 28,if(ISBLANK(_28),0,_28), 29,if(ISBLANK(_29),0,_29), 30,if(ISBLANK(_30),0,_30), 31,if(ISBLANK(_31),0,_31), 32,if(ISBLANK(_32),0,_32), 33,if(ISBLANK(_33),0,_33), 34,if(ISBLANK(_34),0,_34), 35,if(ISBLANK(_35),0,_35), 36,if(ISBLANK(_36),0,_36), 37,if(ISBLANK(_37),0,_37), 38,if(ISBLANK(_38),0,_38), 39,if(ISBLANK(_39),0,_39), 40,if(ISBLANK(_40),0,_40), 41,if(ISBLANK(_41),0,_41), 42,if(ISBLANK(_42),0,_42), 43,if(ISBLANK(_43),0,_43), 44,if(ISBLANK(_44),0,_44), 45,if(ISBLANK(_45),0,_45), 46,if(ISBLANK(_46),0,_46), 47,if(ISBLANK(_47),0,_47), 48,if(ISBLANK(_48),0,_48), 49,if(ISBLANK(_49),0,_49), 50,if(ISBLANK(_50),0,_50), 51,if(ISBLANK(_51),0,_51), 52,if(ISBLANK(_52),0,_52), 53,if(ISBLANK(_53),0,_53), 54,if(ISBLANK(_54),0,_54), 55,if(ISBLANK(_55),0,_55), 56,if(ISBLANK(_56),0,_56), 57,if(ISBLANK(_57),0,_57), 58,if(ISBLANK(_58),0,_58), 59,if(ISBLANK(_59),0,_59), 60,if(ISBLANK(_60),0,_60) ) return _VALUE

Re: Table visual consisting of many measures with DAX (customized P&L)

jameszhang0805 — Sun, 21 Feb 2021 03:20:40 GMT

Dear Draszor:
I found that your index is continuous in the Switch measure. Did you create an individual measure for each P&L category include the category which can be directly filtered to calculate?

Re: Table visual consisting of many measures with DAX (customized P&L)

Draszor — Sun, 21 Feb 2021 12:06:53 GMT

Hi,

I did so, as variables, before the SWICH statement (that is a variable as well, placed in RETURN.

I did so, as my business unit uses a very specific P&L layout, rooted highly in managerial accounting. creating the same payout in PBI, it is enough now that I refresh my basic data, and the firures (margins, ratios in some cases) are there - ready to analyse them.

Re: Table visual consisting of many measures with DAX (customized P&L)

jameszhang0805 — Sun, 21 Feb 2021 14:29:16 GMT

Dear Draszor:
If you created an individual measure for each P&L category, this metric may be less efficient to run, because too many contents transitions happened.

Let me explain my approach:

When an element is not in the fact table, it cannot be filtered directly to calculate, we need to create it individually(Just like NetRev, PM% ..etc).
For the others, just like "400000 - Revenue" which is in the fact table.
CALCULATE( [SumAmount] , TREATAS( VALUES( FinalAccount[Account Code] ) , 'Account'[Account Code] ) )
this code can calculate the result for each P&L category which is in your fact table.
Here I gave the data lineage by the TREATAS function, [SumAmount] can be filtered by P&L category in matrix even there is no relationship between two tables. So even if your P&L Table has a very deep hierarchy, it can still filter the results, every row in the matrix is a filter.