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.
I have a simple data set with columns of data, account code, account name, date (month/year), and account activity. I'm trying to slice the data to get just activity values for accounts of a certain name, but because there are multiple results (one result for each month), I'm having trouble setting up a custom measure or column to pull this information.
Solved! Go to Solution.
Here is what I ended up doing (my table name is 'NOI 2016'):
The final measure gives me my projected annual amount based on YTD figures. In this case, I end up filtering by account name to isolate just NOI.
Here is what I ended up doing (my table name is 'NOI 2016'):
The final measure gives me my projected annual amount based on YTD figures. In this case, I end up filtering by account name to isolate just NOI.
Hey,
I'm wondering what your expected result is ...
Does this simple approach not work for you:
Create a slicer from the column "account name" and a table visual that contains all the columns except "month/year".
The columns will automatically be grouped and by default numeric columns will be "summed".
Regards
Tom
The simple approach wouldn't work because I would like to retain the month/year column.
Sheet 1: this how my data exports
Sheet 2: this is how I modify the data (unpivot to get month/year into a column)
I can easily use this data to produce NOI figures over time, however, I am trying to now get an annualized NOI figure that calculates based on the number of months. For this data set, it's easy enough to add up the 12 months of figures, however, I'd like to create a measure that "knows" how to annualize based on the number of months of data it receives.
For example, if I have data through May 2018, the measure should add up 5 months, get an average and multiply by 12 for an annualized figure.
Hi @nwitstine,
Based on my test, you can refer to below steps:
1.Create a new measure.
Measure = CALCULATE(SUM(Sheet2[Activity]),FILTER(ALL(Sheet2),'Sheet2'[Date]<=MAX('Sheet2'[Date])&& YEAR('Sheet2'[Date])=YEAR(MAX(Sheet2[Date]))))/MONTH(MAX('Sheet2'[Date]))
2. Create a Slicer visual and add the [Date] field. Create a card visual and add the measure. Now you can see the result controlled by the data slicer.
You can also download the PBIX file to have a view.
Regards,
Daniel He
Hi Daniel,
I'm really new to Power BI so I'm not exactly sure what your measure is doing, but when I select all months, I would expect my net operating income to equal the total from sheet 1 (17.51M), however, I am getting 4.35M.
2 things that I would guess are missing:
1. I don't see anything that filters by account name so that the activity included is only for rows where the account name is "Net Operating Income." - I did try adding in an account name slicer for this, but it didn't solve anything.
2. I don't see anything that annualizes the figure (multiplies by 12). It appears to only be taking a monthly average.
Hi @nwitstine,
From your description, you said “For example, if I have data through May 2018, the measure should add up 5 months, get an average and multiply by 12 for an annualized figure.” I guess you want to calculate an average value based on the month, right? If you want to slice the values based on the account name, you just need to modify the formula:
Measure = CALCULATE(SUM(Sheet2[Activity]),FILTER(Sheet2,
'Sheet2'[Date]<=MAX('Sheet2'[Date])&&YEAR(Sheet2[Date])=YEAR(MAX(Sheet2[Date]))&&Sheet2[Account Name]=MAX(Sheet2[Account Name])))
/MONTH(MAX('Sheet2'[Date]))
If you want to see the total value by month, you just need to create a new measure: Total value = SUM(Sheet2[Activity])
Now you can see when you select the all months and the “Net Operating Income”, you could get the total value “17.51M” and the measure shows the average value of the “Net Operating Income”.
Due to I am not sure what value you want to calculate, so I didn’t multiply any values, If I misunderstand you, please just let me know.
You can also download the PBIX file to have a view.
Regards,
Daniel He
Can you post some sample data and your expected outcome ? If you need fix to your currnet measure, post the formula you used as well.
Thanks
Raj
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |