User Profile
gmat
Helper I
Joined 2 years ago
User Widgets
Contributions
How to handle Quarterly Excel and CSV Files
Hi everyone, I need some guidance on the best approach to manage and visualize my data in Power BI. Here’s the situation: Data Sources: I have multiple Excel and CSV files for each quarter, spanning from Q1 2019 to Q1 2024. Each quarter has: An additions file (Excel) – used to track additions to our units. A flat file (CSV) – represents the current mix of units at that point in time. Data Characteristics: There are no dates within the data itself. The data format is consistent across quarters. These files are large and come from an external source, so they don't live in a database. Objective: Additions File: Build a historical view of unit additions from 2019 onwards. Flat File: Track the current mix of units for each quarter. Challenges: Data Integration: Unsure about the best way to integrate these files. Should I import each individual file separately? Will Power BI handle this large dataset efficiently? Historical View: How can I create a matrix with dates as columns to show a historical view when the data resides in different tables? My Current Thoughts: Importing each file individually might be time-consuming and potentially inefficient for large datasets. Combining data from multiple files into a single table for easier analysis and visualization. Questions: What is the best method to handle and integrate these files in Power BI? How can I create a matrix that shows historical data across different quarters when the data is spread across multiple tables?Re: Trying to add Weighted Average Measure to Bar Chart in Power BI
Date Capex Unit Count WACC PCT IRR PCT WACC dollars IRR dollars Weighted avg Spread 3/1/2023 $ 112,000 1 7.80% 8.20% 8,736.00 9,184.00 40 3/1/2023 $ 112,000 1 7.80% 8.60% 8,736.00 9,632.00 80 3/1/2023 $ 112,000 1 7.80% 9.20% 8,736.00 10,304.00 140 3/1/2023 $ 115,000 1 7.80% 9.50% 8,970.00 10,925.00 170 3/1/2023 $ 112,000 1 7.80% 9.50% 8,736.00 10,640.00 170 3/1/2023 $ 112,000 1 7.80% 9.50% 8,736.00 10,640.00 170 3/1/2023 $ 100,000 1 7.80% 9.50% 7,800.00 9,500.00 170 3/1/2023 $ 116,000 1 7.80% 9.50% 9,048.00 11,020.00 170 3/1/2023 $ 112,000 1 7.80% 9.50% 8,736.00 10,640.00 170 4/1/2023 $ 120,000 1 7.80% 9.50% 9,360.00 11,400.00 170 4/1/2023 $ 185,000 2 7.80% 9.50% 14,430.00 17,575.00 170 4/1/2023 $ 185,000 1 7.80% 9.50% 14,430.00 17,575.00 170 4/1/2023 $ 185,000 1 7.80% 9.50% 14,430.00 17,575.00 170 4/1/2023 $ 185,000 1 7.80% 9.50% 14,430.00 17,575.00 170 4/1/2023 $ 185,000 1 7.80% 9.80% 14,430.00 18,130.00 200 4/1/2023 $ 115,000 1 7.80% 8.50% 8,970.00 9,775.00 70 4/1/2023 $ 185,000 1 7.80% 8.20% 14,430.00 15,170.00 40 4/1/2023 $ 100,000 1 7.80% 8.20% 7,800.00 8,200.00 40 4/1/2023 $ 185,000 1 7.80% 8.20% 14,430.00 15,170.00 40 4/1/2023 $ 130,000 1 7.80% 8.20% 10,140.00 10,660.00 40 4/1/2023 $ 185,000 1 7.80% 8.20% 14,430.00 15,170.00 40 4/1/2023 $ 185,000 1 7.80% 8.20% 14,430.00 15,170.00 40 4/1/2023 $ 150,000 1 7.80% 8.20% 11,700.00 12,300.00 40 4/1/2023 $ 112,000 1 7.80% 8.20% 8,736.00 9,184.00 40 4/1/2023 $ 100,000 1 7.80% 8.45% 7,800.00 8,450.00 65 4/1/2023 $ 185,000 1 7.80% 8.45% 14,430.00 15,632.50 65 4/1/2023 $ 185,000 1 7.80% 8.45% 14,430.00 15,632.50 65 4/1/2023 $ 185,000 1 7.80% 8.45% 14,430.00 15,632.50 65 5/1/2023 $ 140,000 1 7.80% 8.45% 10,920.00 11,830.00 65 5/1/2023 $ 60,000 1 7.80% 9.50% 4,680.00 5,700.00 170 5/1/2023 $ 60,000 1 7.80% 9.50% 4,680.00 5,700.00 170 5/1/2023 $ 60,000 1 7.80% 9.50% 4,680.00 5,700.00 170 5/1/2023 $ 60,000 1 7.80% 9.80% 4,680.00 5,880.00 200 5/1/2023 $ 130,000 1 7.80% 8.50% 10,140.00 11,050.00 70 5/1/2023 $ 130,000 1 7.80% 8.20% 10,140.00 10,660.00 40 5/1/2023 $ 130,000 1 7.80% 8.20% 10,140.00 10,660.00 40 5/1/2023 $ 130,000 2 7.80% 8.20% 10,140.00 10,660.00 40 5/1/2023 $ 130,000 1 7.80% 8.20% 10,140.00 10,660.00 40 6/1/2023 $ 140,000 1 7.80% 8.20% 10,920.00 11,480.00 40 6/1/2023 $ 140,000 1 7.80% 8.20% 10,920.00 11,480.00 40 6/1/2023 $ 140,000 1 7.80% 8.20% 10,920.00 11,480.00 40 6/1/2023 $ 140,000 1 7.80% 8.20% 10,920.00 11,480.00 40 6/1/2023 $ 140,000 1 7.80% 8.45% 10,920.00 11,830.00 65 6/1/2023 $ 160,000 1 7.80% 8.45% 12,480.00 13,520.00 65 6/1/2023 $ 160,000 1 7.80% 8.45% 12,480.00 13,520.00 65 6/1/2023 $ 160,000 1 7.80% 8.45% 12,480.00 13,520.00 65 6/1/2023 $ 115,000 1 7.80% 8.45% 8,970.00 9,717.50 65 6/1/2023 $ 160,000 1 7.80% 8.20% 12,480.00 13,120.00 40 6/1/2023 $ 160,000 1 7.80% 8.20% 12,480.00 13,120.00 40Re: Trying to add Weighted Average Measure to Bar Chart in Power BI
This is how I need the visual to look like... However, the current spread field that is list on the Line y axis is just an average of the spread calc for each indivdual unit.. which is not a weighted average. I am trying to include the spread measure highlighted in yellow which is the weighted average DAX expression that i pasted above. The issue is that I am unable to drag this measure into the same spot (line y axis)Trying to add Weighted Average Measure to Bar Chart in Power BI
I'm struggling to include a weighted average measure in a bar chart visualization in Power BI Desktop. This measure is vital for tracking our core performance indicators, and I need to find a way to display it effectively. Issue: Objective: I want to visualize the month-over-month trend of a weighted average measure on a bar chart. Challenge: Although I've created the weighted average measure as a new measure, I'm unable to add it directly to the bar chart. It seems Power BI doesn't allow measures to be added to certain visuals like bar charts. Below is a screenshot of the chart I am working on. The green trend line on the secondary Y axis is what I am trying to replicate.. I have also included the measure I am trying to add into the visualization. Please let me know if anyone has some type of work around for this. Spread = VAR IRR_amt = SUM([IRR_dollars]) VAR capex_AMT = SUM(Capex_dollars]) VAR wacc_amt = SUM(WACC_dollars) RETURN IF( NVI_AMT <> 0, ROUND(((IRR_dollars / capex_dollars) - (WACC_dollars / capex_dollars)) * 10000, 0) & " bps", BLANK() )Formatting Year Over Month in Matrix
I'm currently working on creating a reporting packet in Power BI, and I've encountered a formatting challenge that I hope someone can help me with. I have set up a matrix with a hierarchy where the main column represents the year and the sub-columns represent the months within that year. However, I would like to adjust the formatting so that each month also displays the corresponding year it. For example, instead of just seeing "May" I would like the top column header to also read 2023 instead of being blank. I understand that this is likely a formatting issue, but I'm unsure of the exact steps to achieve this desired layout. Any guidance or suggestions on how to accomplish this would be greatly appreciated.
