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

Making a calculated table

Hi

 

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

Satisfactiontable.png

 

  • 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft 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.

 

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

r1.PNG

 

Here is the modified pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

4 REPLIES 4
kjartank Regular Visitor
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?

Microsoft 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.

 

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

r1.PNG

 

Here is the modified pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

kjartank Regular Visitor
Regular Visitor

Re: Making a calculated table

Hi @v-ljerr-msft

 

You are a genius! Thanks a lot!

 

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

fahadfarooqi Frequent Visitor
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?

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors