Calculations on the fly based on an operations/formulas table

[Lucian]

Hello,

I need some help creating "on fly calculations" based on an operations table (if possible).

My "fact table", reduced to the "basics" contains two "base" columns: a TAG (text) column and an Amount (numeric) column

Based on this fact table I have to create some dynamic reports based on a "calculations table" that will contain a "formula" column that will show what calculations are required based on the tags of the "fact table" and the result should be displayed in the table along formula.

For example, the first formula T1+T2 should add all the values with tags T1 and T2 so the result should be 1800, and the third formula T1/T3 should SUM all the values with T1 tag (1000) and divide to SUM of all T3 tag (200) resulting 5.

This calculation table will be dynamic so I cannot create a measure for each formula because I will not know what the calculation will look like.

Is this achievable in Power BI (M or DAX)?

Kind Regards,

Lucian

[nandic · Accepted solution]

Hi @Lucian ,

Below are steps:
Create measures:

Adding = CALCULATE(SUM('fact'[Amount]),'fact'[Tag] in {"T1","T2"})

Division =

var TotalT1 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag]="T1")

var TotalT3 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag]="T3")

RETURN

DIVIDE(TotalT1,TotalT3)

Subtracting =

var TotalT1T2 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag] in {"T1","T2"})

var TotalT3 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag]="T3")

RETURN

TotalT1T2-TotalT3

Multiplication = CALCULATE(SUM('fact'[Amount])*3,'fact'[Tag]="T3")

After this add new dynamic measure:

Dynamic Measure = SWITCH(TRUE(),SELECTEDVALUE(operations[Order])=1,[Adding], SELECTEDVALUE(operations[Order])=2, [Division], SELECTEDVALUE(operations[Order])=3, [Multiplication], SELECTEDVALUE(operations[Order])=4, [Subtracting])

When you select operation from slicer, it will display only selected measure.

Cheers,
Nemanja

[amitchandak · Accepted solution]

@Lucian , in M you have somthing similar

https://docs.microsoft.com/en-us/powerquery-m/expression-evaluate

also refer : https://github.com/ImkeF/M

[nandic]

Hi @Lucian ,

Follow these steps:
Create measures:

Adding = CALCULATE(SUM('fact'[Amount]),'fact'[Tag] in {"T1","T2"})

Division =

var TotalT1 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag]="T1")

var TotalT3 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag]="T3")

RETURN

DIVIDE(TotalT1,TotalT3)

Subtracting =

var TotalT1T2 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag] in {"T1","T2"})

var TotalT3 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag]="T3")

RETURN

TotalT1T2-TotalT3

Multiplication = CALCULATE(SUM('fact'[Amount])*3,'fact'[Tag]="T3")

Dynamic Measure = SWITCH(TRUE(),

SELECTEDVALUE(operations[Order])=1,[Adding],

SELECTEDVALUE(operations[Order])=2, [Subtracting],

SELECTEDVALUE(operations[Order])=3, [Division],

SELECTEDVALUE(operations[Order])=4, [Multiplication])

Cheers,
Nemanja

[Lucian]

Hi @nandic ,

Thank you for the example, unfortunately is "limited" only to the base sample I have provided. As I said in my initial post I cannot create measures because I don't know what are the "exact calculations" I have to cover.

I'm hope that @amitchandak is not right 🙄...

---

@amitchandak wrote:
I doubt, you can make it fully dynamic

---

... because I need some kind of "fully dynamic" option like Application.Evaluate method (Excel) .

In this case I sould post a "new ideea"?... 🤔

Kind Regards,

Lucian

[amitchandak · Accepted solution]

@Lucian , in M you have somthing similar

https://docs.microsoft.com/en-us/powerquery-m/expression-evaluate

also refer : https://github.com/ImkeF/M

[Lucian]

Hi @amitchandak 

YES!!!! This is the answer I've expected! Thank  you for pointing me into the right direction (and also for the library of @ImkeF ).

Now, I just have to imagine how to "transpose it" in M language, and "generalize it", because is not as simple in Excel with an "A1+B1" (exact address of the cell) but is a good starting point. 😁

Based on your suggestion I have found another article explaining the "environment" option of Expression.Evaluate() In Power Query/M better than MS article, because I have bumped into the "Expression.Error: [1,1-1,3] The name 'A1' doesn't exist in the current context."

Now, with a function like this:

(A1 as any, B1 as any, strFunc) as any=>
let
    RecordOfVariables = [A1=A1, B1=B1],
    RecordOfVariablesAndGlobals = Record.Combine({RecordOfVariables, #shared}),
    result=Expression.Evaluate(strFunc, RecordOfVariablesAndGlobals)
in
    result

I could invoke it directly for expressions like this "A1+B1" or "A1/B1" but I cannot invoke it as a column into a table like this:

I receive the error: "Unexpected error: Operation is not valid due to the current state of the object."

The query is

let
    Source = Excel.CurrentWorkbook(){[Name="tblEvaluatePQ"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A1", Int64.Type}, {"B1", Int64.Type}, {"Formula", type text}, {"Expected resultResult", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fxEval", each fxEval([A1], [B1], [Formula]))
in
    #"Invoked Custom Function"

Could you give me another tip/bump over this step? 😊

Kind Regards,

Lucian

[ImkeF]

Hi @Lucian ,

if you want to refresh the query in the service, you cannot use #shared unforatunately.

But there is good news: It's actually very easy to solve without an extra function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyAmJHQy0nQ20gN1YHRVTfyRAsZALkGYNFtEEisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A1 = _t, B1 = _t, Formula = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A1", Int64.Type}, {"B1", Int64.Type}, {"Formula", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Expression.Evaluate([Formula], _))
in
    #"Added Custom"

Yes, the needed environment record is just a simple underscore. The underscore is some syntax sugar that returns the current row in a record format. Just what you need as the inputs to your formula 🙂

[Lucian]

Hello @ImkeF ,

Amazing as usual... I never thought it could be so simple... when you look at the problem. 😊

Now, is there a way to apply this trick to the "original post tables"? 

The "fact table"

Row_No	Tag	Amount
1	T1	100
2	T2	200
3	T3	200
4	T1	400
5	T1	500
6	T2	600

The "Formula table"

Order	Operation description	Formula	Expected result
1	Adding	T1+T2	1800
2	Subtracting	(T1+T2)-T3	1600
3	Division	T1/T3	5
4	Multiplication	T3*3	600

One ideea it would be to split the Formula column in for each math operator so would result in F1, F2, ... Fn columns and the formulas would be F1+F2 or F1/F3 but I will have one more problem, how to SUM all the corresponding tags from another table to finally EVALUATE the expression? It would be possible in M? And in this case could be a performance problem if all is done in M?

A second ideea is based on two articles found on your site, very close to what I need: Easy Profit & Loss (and other account) statements in PowerBI and Excel – Part2 and KPIs in Easy Profit and Loss for PowerBI.

The first article will solve most of the "simple" calculations like additions and subtractions, and the second should cover the "other type of calculations". The problem is that I expect to be more of the "other types", and unknown "combinations" to prepare all the required measures (as the sample provided by @nandic ) but this time having DAX involved could be an improvement? 

Any (other) ideea?

Kind Regards,

Lucian

[ImkeF]

Hi @Lucian 

I believe that simply pivoting the amount column would provide the structure you need?

[Lucian]

Hello @ImkeF ,

Indeed, a simple pivot by Tag column will have the "Fact table" prepared to use the formula:

Row_No	T1	T2	T3
1	100
2		200
3			200
4	400
5	500
6		600

But the formula is in the other table, the "formula table". 

Also I think I have miss the fact that some calculations will need some filters/slicers so I cannot do it exclusively in M, and I have to consider DAX.

And in this case I think I have to use the other scenario presented by you in KPIs in Easy Profit and Loss for PowerBI even I have to loose the "fully dynamic" calculations I was looking for, only if by any chance is possible to have an Expression.Evaluate in DAX. 😁

Kind Regards,

Lucian

[ImkeF · Accepted solution]

Hi @Lucian ,

You would have to add another column to the report layout table that holds the code for the formula that shall be used.

Currently the only calculation being done is "DIVIDE":

Based on the new "formula"-column you have to create another switch-measure that defines the relevant formula.

There is a sample file in the article that should get you starting. But don't expect it to be easy. It will probably take some time to get your head around it.

[Lucian]

Thank you all for your answers. Now I should be on the right track.

Kind Regards,

Lucian

[nandic · Accepted solution]

Hi @Lucian ,

Below are steps:
Create measures:

Adding = CALCULATE(SUM('fact'[Amount]),'fact'[Tag] in {"T1","T2"})

Division =

var TotalT1 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag]="T1")

var TotalT3 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag]="T3")

RETURN

DIVIDE(TotalT1,TotalT3)

Subtracting =

var TotalT1T2 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag] in {"T1","T2"})

var TotalT3 = CALCULATE(SUM('fact'[Amount]),'fact'[Tag]="T3")

RETURN

TotalT1T2-TotalT3

Multiplication = CALCULATE(SUM('fact'[Amount])*3,'fact'[Tag]="T3")

After this add new dynamic measure:

Dynamic Measure = SWITCH(TRUE(),SELECTEDVALUE(operations[Order])=1,[Adding], SELECTEDVALUE(operations[Order])=2, [Division], SELECTEDVALUE(operations[Order])=3, [Multiplication], SELECTEDVALUE(operations[Order])=4, [Subtracting])

When you select operation from slicer, it will display only selected measure.

Cheers,
Nemanja

[amitchandak]

@Lucian , I doubt, you can make it fully dynamic. Based on operation selected you can change, refer this example

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...