cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Divide current months value by average of monthy totals YTD, still enabling a slicer

I'm at my wits end lol. I have to divide totals of values from the current month by the average of the totals of the months to date.
Example:

 Months Total of value by month Current month (April) 10 March 5 February 0 January 10

So, average of totals is: (5+10)/2 = 7.5
Then I have to get: [Total of current month]/[average of totals] = 10/7.5 = 1.333

So far so good, but there must be a date slicer on the page, through wich the user will be able to select which month to analyze and the division will be of the YTD, so If I slice "March", the average of totals will be of February and January. And if I selected a month from a different year, the measure has to get the YTD corresponding to that year.

Problem is, since the slicer is always filtering just a single month on the page, the average would always return just the value of the current month, no matter if I used ALLEXCEPT, ALL or REMOVEFILTERS, it wouldnt aggregate the year and do an average of the sum by month (I was using a SUMMARIZE by month, then average, something like below).

Intended Average Volume =
VAR date2 = MAX(d_calendar[Data])
VAR year = YEAR(date2)

VAR table1 = CALCULATETABLE(SUMMARIZE(f_fact,f_fact[month],"volume",SUM(f_fact[volume])),f_fact[year]=year)
RETURN
AVERAGEX(table1,[volume])

I ended up creating a calculated table, disconnected from my date table, using the following measure the get the average in a way that would ignore the slicer:

Average Volume =
VAR date = MAX(d_calendar[Date])
VAR year = YEAR(date)
VAR month = MONTH(date)
VAR calc =
CALCULATE(
AVERAGE(clients[volume]), clients[year] = year
)
RETURN
calc

This works, BUT not with RLS, sigh. I'm sure I'm missing something very simple but I'm so deep I can't see straight anymore. How do I divide current months value by the average of monthy totals YTD, still enabling a slicer to work as described?

Community Support

Hi @dlopesc04 ,

First of all, you definitely need to use an unrelated date table to do the calculations you want, but to implement RLS, you probably need TREATAS() to pass the filter. Here's a tutorial for you:DAX limitations with inactive relationships and row-level security (RLS) - SQLBI

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors