To Whom It May Concern:
I’ve provided both an Excel sheet and a PBIX workbook that showcases the problem I am currently having.
Initially, I wanted to create a parameter that took into account the change from the previous row. After many attempts, I found Melissa’s thread here. This worked beautiful and the output was exactly what I needed.
The data in dim_macro is simple. It takes into account the year and the expected headcount of a business unit. The parameter lets the user play with a growth factor and see the change from 2024 to 2030.
When I break down the headcount per year based on job category, I’m am having difficulty using the DAX formula Melissa wrote to reflect the matrix (job category, by year, based on the value).
Would anyone care to weigh in on what I can or should do to move forward? Should I structure my Excel sheet differently or is there something in the DAX formula I need to consider?
Greatly appreciate any help or advice and I’d be happy to elaborate on the problem. Please view the attachments for more info.
Best,
Luis
dim_data.xlsx (10.1 KB)
question1.pbix (30.0 KB)
1 Like
Change it to “dim_micro” to see if it works
Hi Vilmar,
Thanks for much for taking a look at it. That is indeed an error in my example file. Unfortunately, it doesn’t add up correctly. Screenshot below:
Hi,
Could you try the following :
Forecast Org 2 =
VAR Base = MINX( ALL(dim_micro[Headcount]),dim_micro[Headcount])
VAR vTable =
ADDCOLUMNS(SUMMARIZE(dim_micro, dim_micro[Job Category], dim_micro[Year]), – we need to insert the dimensions used by the matrix. SUMMARIZE returns only valid tuples, so a job category can start after the others
“@Value”,
VAR __Category = [Job Category] -- EARLIER seems to be deprecated ; I preferer using variables, 1 per dimension
VAR __Year = [Year]
VAR __TableCY = FILTER(ALL(dim_micro),
dim_micro[Job Category] = __Category && dim_micro[Year] <= __Year) -- I search the table for the same job category for years before
-- if % of increase applies starting year 2, put <. If % applies year 1, put <= above for comparison with Year
VAR __Data = MINX(__TableCY, [Headcount])
-- we have a line too many because the first year should not be used for exponentiation
VAR __TableCY2 = FILTER(__TableCY, dim_micro[Year] < __Year)
-- we apply the µ% of increase starting year 2 : the two syntaxes are working
--RETURN IF(COUNTROWS(__TableCY2)=0, 1, PRODUCTX(__TableCY2, 1+ SELECTEDVALUE(Forecast[Forecast]))) * __Data
RETURN __Data * POWER(1+ SELECTEDVALUE(Forecast[Forecast]) , COUNTROWS(__TableCY2))
)
RETURN SUMX(vtable, [@Value])
Fabrice
Hi,
I can also copy the pbix file. Could be easier for you
Fabrice
question1.pbix (33.5 KB)
