Calculate Difference in Columns

DAX DAX Calculations
1

Hi Fellow Power BI Users -

I feel lost with the problem I’m expericing on this model.

The aim of this model is to be able to compare quotations from different suppliers for every purchasing round, and every Project GPN number.
During the Purchasing negotiotation you may have manifold interactions with suppliers. Consequently The list of suppliers may vary in every round (some of them leave, others are added after)…

As consequence this is a dynamic model, this means for GPN XXXXXX you may have supplier 1, 2 and 4, but they may differ for Project GPN YYYYYY… so you can’t use calculations like Calculate (TCO, Supplier Name = “Supplier 1”) or similar…

I was able to solve this problem when only two quotation appeared in a determined Project GPN and Round, calculating the difference between the SELECTEDVALUE and the TOTAL VALUE using ALL function like follows:

Difference % = 
VAR SupplierRef = SELECTEDVALUE(Tabla2[Walkround]; BLANK())
VAR TOTALValueSelected = CALCULATE(SUM(Tabla2[Total Price (Calculated)]); Tabla2[Walkround]= SupplierRef)
VAR TotalValueVendors = CALCULATE(SUM(Tabla2[Total Price (Calculated)]); ALL(Tabla2[Walkround]))
RETURN
IF(TOTALValueSelected - (TotalValueVendors-TOTALValueSelected) < 0 ; "-Δ" & FORMAT(-1*(TOTALValueSelected - (TotalValueVendors-TOTALValueSelected)) / TotalValueVendors;"Percent"))

Capture2
Capture21434×514 23.5 KB

For this Round you have received 2 quotations from 2 different suppliers. To compare, you would have to find the lowest value and calculate the “delta” (difference) with the other more expensive quotations.

On top of this, we have two Row Level, Main Category and Sub Category, where you can show/hide (or drill down). I was doubting if a ISFILTER condition might be needed like we did with a Rank Scenario;

Rank with 2 levels

Could you help me to build de DAX Measure considering the Levels in Rows, and having more tan 2 quoation in a Project and Round?

Capture
Capture792×473 15.9 KB

2

Just based on what you’ve shown here I really don’t have any idea where to start.

There’s a lot that would need to be done right. This included the model you have the data table setup etc. This is very important.

Just by looking at this table I really have no idea how to assist right now.

My recommendation is to really start with your data, what does that look like? Then understand how your model is setup, then start on the formula.

3

Hi

Apologies for not replying you on this post.
I was able to solve the problem using TOPN function. With this function you can “find” the best quotation from all suppliers involved in the quotation process, and then compare with that TOPN (1) quotation and get the difference between offers (delta).

Unfortunately my file has (this time), confidential information, so I cannot upload as I usually do with Dummy files.

Kind regards,