Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I am trying to make a table that looks like this in Power BI.
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!
Solved! Go to 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
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
I need the list of formulas for Power BI.
Like they do in excel.
Where Can I get it?
You are a genius! Thanks a lot!
A last question. How would I tell PBI to exclude the blanks?
If it is impossible ti make such a table, what would be a good way to show this data?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |