0

I want to create a dynamic text (Visual - Enlighten Data Story) that will write the measures X, Y and Z (see pictures below).

X is the Activity that had the most energy consumption on that city (one of those: Industry, Transports, Services, etc etc) Y is the value of that energy consumption Z is the percentage between Y and the Total consumption of that City

For this example, in the City of Porto that text should return:

X = Edificios de Serviços Y = 589.976 Z = 49%

example Porto

If I select any other city, for example Guimarães:

X = Industria Y = 428.726 Z = 59%

example Guimarães

Here is my table:

table

Looking forward for help. Thank you very much.

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Patrícia Silva
  • 3
  • 1
  • 2

1 Answers1

0

This should get you started. Important is to know the index of the values and Rank does that:

//Calculated column
Rank =
VAR City = Table1[City]
RETURN
    RANKX (
        FILTER ( Table1, Table1[City] = City ),
        [Energy_Mwh],
        [Energy_Mwh],
        ASC,
        DENSE
    )

//Calculated field/measure
City-as Measure =
LASTNONBLANK ( Table1[City], 1 )

//Calculated field/measure
x = CALCULATE(LASTNONBLANK(Table1[Activity],1),filter(all(Table1[Rank]),Table1[Rank] = max(Table1[Rank])))

//Calculated field/measure
y = CALCULATE(LASTNONBLANK(Table1[Energy_Mwh],1),filter(all(Table1[Rank]),Table1[Rank] = max(Table1[Rank])))

Thanks

Prakash Gautam
  • 502
  • 4
  • 13
  • Thank you sooo much! you are a life saver, that solution worked perfectly and did exactly what I wanted! =) I am still a begginner at Power BI, and I dont think I could ever do that by myself. – Patrícia Silva May 01 '18 at 18:24
  • Thank you. Please also see: https://stackoverflow.com/help/someone-answers – Prakash Gautam May 02 '18 at 07:12