Variances between multiple columns and return lowest value

[Anonymous]

Hi There,

I am seeking some support for a DAX expressions which will measure variance between 4 - 5 columns on a data table and return the lowest value. For example

 mODEL | REGION | CURRENT PRICE | VENDOR 1 | VENDOR 2 | VENDOR 3 | VENDOR 4 | LOWEST PRICE | VARIANCE TO CURRENT |

XX1            WA          102.00                 96.00           75.00              76.00          87.00             ??                         ??

I am currently doing tender reviews of pricing tables received from competitors and want to use POWER BI to load the data and use DAX expressions to help with the review.

I guess part 2 is a little simpler in that I would then need to calculate the variance (+ or - ) against the volume of each model planned to be sold. (measured against the sales plan.

Would appreciate any help you could offer.

Many thanks & regards

Fabian (DAX HANDICAPPED)

[Ashish_Mathur]

Hi,

My advise would be to first use the "Unpivot Other columns" feature of the Query Editor to convert your dataset into a 4 column one - Model, Region, Attribute and Prices.  For every Model/Region combination there would be five rows - Current Price,Vendor1,Vendor2,Vendor3 and Vendor4.

In your visual drag Model and Region to the Row labels.  Then try the following measures:

Lowest Price = CALCULATE(MIN(Data[Value]),Data[Attribute]<>"Current Price")

Variance to current = [Lowest Price]-CALCULATE(MIN(Data[Value]),Data[Attribute]="Current Price")

Hope this helps.

[Anonymous]

Hi Ashish,

Thank you for your speedy reply.

So to begin with I have some other challenges, which i think actually stems from the structure of the original data table.

In the visual when I use the details of the model, region, Qty sold (which exists in one datatable) If I try to drag pricing into the same table us select price for example it shows a blank value.

All the tables have a relationship to the sales data table through the primary key, or could be linked through the model number itself.

I did as you advised, which was to unpivot other columns. Regardless, I still can't get the prices even to reflect against the models from a different dataset.

Are you able to advise on this?

Thank you so much

[Ashish_Mathur]

Hi,

The question in your most recent reply is different from the one you posted initially.  In the initial post, you just had one Table but in your most recent post, you have more than 1 Table.

Please share the link from where i can download your PBI file and also show the expected result there.

[Zubair_Muhammad · Accepted solution]

@Anonymous

With Query Editor, it is much easier to add a Minimum Value Column

Select all the Vendor Columns>>> Go to "Add Column" Tab>>> Statistics >>>Minimum

[Zubair_Muhammad]

@Anonymous

If you want to use DAX and do not want to unpivot and have few VENDORS like 4-5 to compare....
then you can also use following calculated Column

MinValue =
MIN (
    MIN ( MIN ( TableName[VENDOR 1], TableName[VENDOR 2] ), TableName[VENDOR 3] ),
    TableName[VENDOR 4]
)