Making a calculated table

[kjartank]

Hi

I am trying to make a table that looks like this in Power BI.

• The first column is the values from "Booking - Where"  from the dataset.
• The Score column is the average score for each of these values, from the most recent quarter.
• The "+/-" is the averages from the two past quarts, that is subtracted from the average from the quarter that is being analyzed.
• The last column is the percentage of customers that are satisfied, but only from this quarter.

One of the problems I face is that I can't works with calendardata, as we have the quarters and a fifth period in the summer. I just can't seem to figure out how I should be taking this on.

Here is a link for the datafile, if it is to any help. Table data

I would be very grateful for any help!

Thanks in advance!

[v-ljerr-msft · Accepted solution]

Hi @kjartank,

If I understand you correctly, you should be able to use the formulas below to get your expected result.

1. Add a new calculate column "QuarterNO" to your table.

QuarterNO = 
VALUE ( LEFT ( Tabel2[Quarter], 4 ) ) * 4
    + VALUE ( MID ( Tabel2[Quarter], 6, 1 ) )

2. Create different measures to calculate "Score", "+\-", and "Satisfied%", then show them on the Table visual with the "Booking-Where" column.

Score = 
VAR latestQuarter =
    CALCULATE ( MAX ( Tabel2[QuarterNO] ), ALL ( Tabel2 ) )
RETURN
    CALCULATE (
        AVERAGE ( Tabel2[Booking - Satisfaction] ),
        Tabel2[QuarterNO] = latestQuarter
    )

+/- = 
VAR latestQuarter =
    CALCULATE ( MAX ( Tabel2[QuarterNO] ), ALL ( Tabel2 ) )
RETURN
    CALCULATE (
        AVERAGE ( Tabel2[Booking - Satisfaction] ),
        Tabel2[QuarterNO]
            >= latestQuarter - 2
            && Tabel2[QuarterNO]
            <= latestQuarter - 1
    )
        - [Score]

Satisfied% = 
VAR latestQuarter =
    CALCULATE ( MAX ( Tabel2[QuarterNO] ), ALL ( Tabel2 ) )
RETURN
    DIVIDE (
        CALCULATE (
            COUNTROWS ( Tabel2 ),
            FILTER (
                Tabel2,
                Tabel2[QuarterNO] = latestQuarter
                    && Tabel2[Booking Satisfied] = "Satisfied"
            )
        ),
        CALCULATE (
            COUNTROWS ( Tabel2 ),
            FILTER ( Tabel2, Tabel2[QuarterNO] = latestQuarter )
        )
    )

Here is the modified pbix file for your reference. 

Regards

[fahadfarooqi]

I need the list of formulas for Power BI. 

Like they do in excel.

Where Can I get it?

[kjartank]

Hi @v-ljerr-msft

You are a genius! Thanks a lot!

A last question. How would I tell PBI to exclude the blanks?

[kjartank]

If it is impossible ti make such a table, what would be a good way to show this data?