Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Variances between multiple columns and return lowest value

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)

1 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

 

7601.png


Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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

 

7601.png


Regards
Zubair

Please try my custom visuals

@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]
)

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.