cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nwitstine Frequent Visitor
Frequent Visitor

Slicing accounting values based on account name

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
nwitstine Frequent Visitor
Frequent Visitor

Re: Slicing accounting values based on account name

Here is what I ended up doing (my table name is 'NOI 2016'):

 

  1. I created a calculated column: Month = 'NOI 2016'[Date].[Month]
  2. And a measure that counts the number of months: Months = countx('NOI 2016','NOI 2016'[Month])
  3. A measure that calculates the YTD activity: YTD = totalytd(sum('NOI 2016'[Activity]),'NOI 2016'[Date].[Date])
  4. A measure that averages the YTD activity of the applicable number of months: YTD Avg = DIVIDE('NOI 2016'[YTD],'NOI 2016'[Months])
  5. A measure that annualizes the average for 12 months (I could only figure out how to work this using the divide function, so instead of multiplying my avg by 12, I divide by 1/12 to get the same result): YTD Annualized = DIVIDE('NOI 2016'[YTD Avg],1/12)

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.

View solution in original post

7 REPLIES 7
rajendran Super Contributor
Super Contributor

Re: Slicing accounting values based on account name

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

Super User III
Super User III

Re: Slicing accounting values based on account name

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
nwitstine Frequent Visitor
Frequent Visitor

Re: Slicing accounting values based on account name

The simple approach wouldn't work because I would like to retain the month/year column.

 

Here is some sample data

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. 

Microsoft v-danhe-msft
Microsoft

Re: Slicing accounting values based on account name

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]))

1.PNG

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.

2.PNG

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/0la4wsxe6sydaad/Slicing%20accounting%20values%20based%20on%20account%20nam...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nwitstine Frequent Visitor
Frequent Visitor

Re: Slicing accounting values based on account name

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.

Microsoft v-danhe-msft
Microsoft

Re: Slicing accounting values based on account name

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])

1.PNG

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.

https://www.dropbox.com/s/mzbe7lkmivbx9rp/Slicing%20accounting%20values%20based%20on%20account%20nam...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nwitstine Frequent Visitor
Frequent Visitor

Re: Slicing accounting values based on account name

Here is what I ended up doing (my table name is 'NOI 2016'):

 

  1. I created a calculated column: Month = 'NOI 2016'[Date].[Month]
  2. And a measure that counts the number of months: Months = countx('NOI 2016','NOI 2016'[Month])
  3. A measure that calculates the YTD activity: YTD = totalytd(sum('NOI 2016'[Activity]),'NOI 2016'[Date].[Date])
  4. A measure that averages the YTD activity of the applicable number of months: YTD Avg = DIVIDE('NOI 2016'[YTD],'NOI 2016'[Months])
  5. A measure that annualizes the average for 12 months (I could only figure out how to work this using the divide function, so instead of multiplying my avg by 12, I divide by 1/12 to get the same result): YTD Annualized = DIVIDE('NOI 2016'[YTD Avg],1/12)

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.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors