cancel
Showing results for
Did you mean:
Highlighted
kjartank Regular Visitor

## Making a calculated table

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!

1 ACCEPTED SOLUTION

Accepted Solutions v-ljerr-msft
Microsoft

## Re: Making a calculated table

Hi @kjartank,

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

```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

4 REPLIES 4
kjartank Regular Visitor

## Re: Making a calculated table

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

## Re: Making a calculated table

Hi @kjartank,

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

```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

kjartank Regular Visitor

## Re: Making a calculated table

You are a genius! Thanks a lot!

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

fahadfarooqi Frequent Visitor

## Re: Making a calculated table

I need the list of formulas for Power BI.

Like they do in excel.

Where Can I get it?

Announcements #### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest! #### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system. #### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge. #### Power Platform Online Conference 