powerbidaxmeasures

Dynamic Text in Power BI - Measures to support the Text

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.

Solution

  • 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