Need to show min max and average but also need to show which country is linked to value.

[gravels1]

Hi,

I have been tasked with my very first dashboard and my superiors want me to highlight some KPI's on the very first page.

Essentially, I have a few different KPI...for example: Full time employee (FTE) per square meters. I work for a gouverment ministry so those KPI's would be for Missions (Countries).

I need to have some type of simple visual that not only shows the value (i.e 30 square metter per FTE) but also the Mission that is linked to it. 

It cannot be a table, as they want something more esteticaly pleasing, like a card...but card can only have a single data field. 

Something like a gauge would be nice. Anything really.

How do I go about this? 

Thank you 

[FreemanZ]

hi @gravels1 

for cases like yours, i would create a Card visual for each index and align them like a whole piece. 

[gravels1]

@FreemanZ  Hi,

Thanks for you reply, justy to be clear...

You would do a card with max value for 'Area per FTE' , then right beside it put another card with the Mission's name?

May seem like a stupid question but how do I make sure the second card 'Mission' diplays the correct mission associated with the first card 'max value of Area per FTE'?

Thank you!

[FreemanZ]

hi @gravels1 

you can put the mission's name as the Card label.

https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-kpi?tabs=powerbi-desktop

[gravels1]

@FreemanZ While this is a great solution, the issue is that I am working with live data that changes often. While it may work for this example in particular, if the kpi value changes and now another mission has the highest 'Area per FTE' I would have to manually change it everytime. While this works, it is not optimal.

Sorry I should have definitely made that clear earlier.

[FreemanZ]

hi @gravels1 

the card label can be dynamic.

https://community.powerbi.com/t5/Desktop/Dynamic-title-on-card-visual-card/td-p/2076392

[gravels1]

@FreemanZ Oh! Now your are onto something.

I am not sure I fully understand the solution in the link you have provided though.

I need to make some type of formula for it to work correct? Because when I click on fx I only have the option of first or last...

What formula should I be using so it produces max instead of first?

[FreemanZ]

hi @gravels1 

you would need a measure to feed the fx. You shall be able to choose the measure when you click "last mission CD" in your screenshot. 

for example:

i have a simple dataset like this:

the measure to feed the value of the card visual:

MaxAmt = MAX(TableName[Amount])

the measure to feed the title of the card visual:

MaxSales = 
VAR _MaxSales = 
MINX(
    FILTER(TableName, TableName[Amount]=[MaxAmt]),
    TableName[Sales]
)
RETURN
"Top Sales is " &_MaxSales

it worked like this:

is that what you expect?

[gravels1]

@FreemanZ This is exactly what I am trying to acheive.

I am so so sorry man but I am having issue reproducing your formula. Power Bi is telling me my VAR syntax is wrong.

Here is a picture of a table I will be using for my example:

So the first formula that would feed the card woule be (the name of the data set is called MAP):

Max Area/FTE = Max(MAP[Net Area divided by Total FTE])

But how should I write the second one? Using Mission CD and Net Area divided by Total FTE, both field are in the MAP data set.

I am so sorry, I am having such a hard time with this. I greatly appreciate your help.

[FreemanZ]

hi @gravels1 

try like:

Mission CD of Max Area/FTE =

VAR _MaxCD = 

MINX(

    FILTER(TableName, TableName[Net Area divided by Total FTE]=[Max Area/FTE]),

    TableName[Mission CD]

)

RETURN

"Mission CD of Max Area/FTE: " &_MaxCD

[gravels1 · Accepted solution]

Everyone, I have the solution.

First I needed to add a new column to add 'Mission CD' to my net area table.

Then using this formula : 

TOP =

VAR TempTable =

GROUPBY('#Property', '#PROPERTY'[Mission Symbol], "Max Area", SUMX(CURRENTGROUP(), '#Property'[Net Area]))

RETURN

MAXX(TOPN(1, TempTable, [Max Area],DESC),'#PROPERTY'[Mission Symbol])

It worked.

Thank you all for your help.

[gravels1]

Hi @FreemanZ ,

I tried all type of different ways to make this formula work yesterday but all it does it spit out the first (or last if using MAXX) Mission CD in my spreadsheet.

Looking closer at your firt exemple a little higher up this thread I realised that your formula was unfortunitely not working.

Instead of Top Sales is ''A'' , it should have been ''D'' since is is the letter associated with the highest value which is 6.

I am sorry I did not notice that earlier.

[gravels1]

This is what it is looking like for me currently.

[amitchandak]

@gravels1 , Check Multirow card

Check - https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381083?exp=ubp8

[gravels1]

Hi,

It is ok. But I really want to only show the max value and the min value. I do not want to see the rest.

[gravels1]

Those would be two data feilds I would need in my example