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! Thanks to anyone who can help in advance. I have to replicate a visual that makes the most sense as a multi-row card - and this works, but cards can't be right justified. Due to my client needing it to be right justified, I am unsure how to best accomplish this. I need to have columns for "Previous Date Range" and "Selected Data Range," and rows for spend LY/spend (sum), Bookings LY/Bookings (sum), Avg Seg Price LY/Avg Seg Price, and Online LY/% Online Spend. Is there a different way to make the measures to get the data organized this way with a table or matrix that I'm blanking on?
I have attached screenshots of the visual, and the data used (most are measures that I created). From my database, I have Spend, Bookings, and Online/OfflineT. I have a basic calendar table. The multi-row card is repeated multiple times because I have filtered it on a different metric for each (air, rail, hotel, etc). Because I need to be able to filter the current data and compare to same period last year, I created measures for this. I have a slicer to select date range, and then not filter out the previous date range.
Data from data source, summed: Spend, Bookings, Online/OfflineT (this is a 1 for booked online or 0 for booked offline)
Created Measures:
Spend LY = CALCULATE(SUM(Query1[Spend]),DATEADD('Calendar'[Date],-12,MONTH))
Bookings LY = CALCULATE(SUM(Query1[Bookings]),DATEADD('Calendar'[Date],-12,MONTH))
Avg Seg Price LY = DIVIDE(CALCULATE(SUM(Query1[Spend]), DATEADD('Calendar'[Date], -12,MONTH)), CALCULATE(SUM(Query1[Bookings]), DATEADD('Calendar'[Date], -12,MONTH)),0)
Avg Seg Price = DIVIDE(SUM(Query1[Spend]),SUM(Query1[Bookings]),0)
Online LY = CALCULATE(SUM(Query1[Online/OfflineT]),DATEADD('Calendar'[Date],-12,MONTH))
(For the Delta Change)
% Change (Spend) = DIVIDE(SUM(Query1[Spend])-CALCULATE(SUM(Query1[Spend]), DATEADD('Calendar'[Date],-12,MONTH)), CALCULATE(SUM(Query1[Spend]), DATEADD('Calendar'[Date],-12,MONTH)))
% Change (Bookings) = DIVIDE(SUM(Query1[Bookings])-CALCULATE(SUM(Query1[Bookings]), DATEADD('Calendar'[Date], -12,MONTH)), CALCULATE(SUM(Query1[Bookings]), DATEADD('Calendar'[Date],-12,MONTH)))
% Online Spend = DIVIDE(CALCULATE(SUM(Query1[Spend]), Query1[Online/OfflineT]=1), SUM(Query1[Spend]),0)
% Change (Avg$) = DIVIDE(DIVIDE(SUM(Query1[Spend]),SUM(Query1[Bookings]),0)-DIVIDE(CALCULATE(SUM(Query1[Spend]), DATEADD('Calendar'[Date],-12,MONTH)), CALCULATE(SUM(Query1[Bookings]), DATEADD('Calendar'[Date],-12,MONTH)),0), DIVIDE(CALCULATE(SUM(Query1[Spend]),DATEADD('Calendar'[Date],-12,MONTH)), CALCULATE(SUM(Query1[Bookings]), DATEADD('Calendar'[Date],-12,MONTH)),0))
% Change (Online) = DIVIDE(SUM(Query1[Online/OfflineT])-CALCULATE(SUM(Query1[Online/OfflineT]), DATEADD('Calendar'[Date], -12,MONTH)), CALCULATE(SUM(Query1[Online/OfflineT]), DATEADD('Calendar'[Date],-12,MONTH)))
Hi @Anonymous,
Firstly, if you want the Multi-row to have this feature, you could submit an idea here: power-bi-ideas.
Secondly, I would suggest you read this blog: http://hectorv.com/table-or-matrix-for-side-by-side-column-comparison-with-columns-with-different-formats-in-the-same-column-updated. There is a good workaround.
BTW, how to create a multi-row visual like yours? Could you please share it with me?
Best Regards!
Dale
Hi Dale! @v-jiascu-msft
My multi-row visual just takes lots of components to manually build it. I do use a lot of ctrl-C and ctrl-V to more quickly build and format it. In the photo below, A = "insert" individual text boxes, B = multi-row cards (each box is one multi-row card), and C = "insert" line, then repeat it for the rest of the "columns." Hope this helps! If you have further questions, please let me know.
Hi @Anonymous,
Thank you for sharing. You are so nice. This is very helpful. The visual is perfect. I almost believe it's a special visualization.
Best Regards!
Dale
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |