Forum Discussion
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!
Thanks in advance!
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. :smileyhappy:
Regards
4 Replies
If it is impossible ti make such a table, what would be a good way to show this data?
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. :smileyhappy:
Regards
Hi v-ljerr-msft
You are a genius! Thanks a lot!
A last question. How would I tell PBI to exclude the blanks?
I need the list of formulas for Power BI.
Like they do in excel.Where Can I get it?
