PowerBI If Then & Conditional Format

[MuthalibAbdul]

Hello Everyone,

So I am trying to compare last two column (avg 0f 2021 and avg of 2024) to assign the last column with low/high. Basically, if the avg of 2024 is less than or equal to avg of 2021, then assign a "Low". However, the logic does not meet since I get low for rows where the value of 2024 is actually high. Also, both the 2021 and 2024 columns are in two diff tables. If there is a easier way to conditional format the entire row with the same logic, that would help as well and save me one column (the last one)

Thanks in advance

[MuthalibAbdul]

https://app.powerbi.com/groups/ac952f19-b761-4130-a846-a24d4b001979/list?experience=power-bi

I have uploaded  a test pbi file on microsoft fabric. Please let me know if you can see it. No other way to share a file since I am not a pro user yet. 

Please not that 2024 data )can be viewed in semantic models) does not have average. Hence, I created a new measure in the same data set. The 2021 on the other hand has only averages. If you see the final output, there are 2 hospitals with less average in 2024 than in 2021. I would like them both to be highlighted red or atleast add a new column that saw low for low numbers in 2024 or if there is a way to show red down arrow and green up arrow, that would be great too. 

I am sure this is a simple solution for you but It took 3 days already and I am not even close to a solution. 

[v-yohua-msft]

Hi, @MuthalibAbdul 
I'm sorry to reply to you so late, I don't have permission to view the content you posted, maybe you can share your PBIX files through Google drive or SharePoint and OneDrive, while taking care of your privacy.

Best Regards

Yongkang Hua

[v-yohua-msft]

Hi, @MuthalibAbdul 

To compare values in two different tables, you first need to make sure that there is a relationship between the tables. If you don't have a direct relationship that allows for direct comparison, you might consider creating a calculated column that ingests the necessary data into a single table or uses DAX measures to perform the comparison.

You can create a calculated column in either table, preferably the one used for the final visualization, to take the 2024 average and compare it to the 2021 average. Here's a simplified example:

ComparisonResult = 
VAR Avg2024 = CALCULATE(AVERAGE(Table2024[Value]), ALL(Table2024))
VAR Avg2021 = CALCULATE(AVERAGE(Table2021[Value]), ALL(Table2021))
RETURN
IF(Avg2024 <= Avg2021, "Low", "High")

If you can, please provide more details with your desired output and pbix file without privacy information (or some sample data)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.