Compare SUM of Columns and return biggest as custom string

[MikaelB]

Hi 🙂

How to pick best performing column from multiple columns.
I have multiple tables: "google", "facebook" etc.
Every table have a "impressions" column that show impressions per date.

On my dashboard i have slicer for date. So SUM values are dynamic for each "impressions" column.
GOAL: I want to have dynamic card visual called "Strongest platform" that shows strongest platform as string, like "Facebook".
QUESTION: How to compare SUM of each column and return the biggest one as string?

Thank you.

[MikaelB · Accepted solution]

Due heavy workload i'm terribly sorry for late answer.
For lack of time i could not prepare model to show here.

BUT. I managed to fix the issue by using dimension table relations. Date and Platform dimensions are in use and are connected to fact tables.

Solution:
Made card visual that shows Platform "name" filtered with TOPN 1 by measure. Measure calculated sum of all impression columns from different platform fact tables. Now, even when using slicer, it works like a charm.

Thank you for your time. And never have i seen this fast answers in any community 🙂 This is something else.

[Icey]

Hi @MikaelB ,

If the structure of all tables is the same, it is recommended to merge tables.

If not, it is recommended to create a new Date table, and then establish a relationship between each of the tables and this Date table.

Best Regards,

Icey

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

[amitchandak]

@MikaelB , need to know your data model for that. Better they are as rows (Facebook, Google), etc . Not as columns

https://radacad.com/pivot-and-unpivot-with-power-bi

[Greg_Deckler]

@MikaelB - There are various techniques for things like this like MC Aggregations, Lookup Min/Max, etc. but need to better understand how your data is laid out. 

For example, you could do this:

Measure = 
  VAR __Facebook = SUM('Facebook'[Impressions])
  VAR __Google = SUM('Google'[Impressions])
RETURN
  SWITCH(TRUE(),
    __Facebook > __Google,"Facebook",
    "Google"
  )

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.