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.
Hi,
I created a measure to calculate the cumulative actuals based on the query new business basic. What is the suggested approach to make this measure adaptive to be used in a Matrix using different fields of the NewBusinessBasic query as columns and rows, e.g. products, vendors?
Hi @Anonymous ,
What error did you meet when you drag the fields into the matrix? Was it like this?
If yes , you can change the data type:
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Lionel, thanks for the help, no specific error but the number simply don't adjust to the columns and rows of the matrix. Which is not suprising, because the DAX statement needs adjustetment, psydo code:
AnnualPremiumCum = CALCULATE(SUM('NewBusinessBasic'[AnnualPremium]); FILTER(ALLSELECTED('NewBusinessBasic');'NewBusinessBasic'[Policy_StartDate] <= MAX('NewBusinessBasic'[Policy_StartDate]; CONSIDER Matrix Rows, CONSIDE Matrix columns)))
Hi @Anonymous ,
Maybe you can try this DAX:
In general, when we calculate the cumulative we need to use the 'EARLIER()' function.
AnnualPremiumCum =
CALCULATE(
SUM('NewBusinessBasic'[AnnualPremium]),
FILTER(
ALLSELECTED('NewBusinessBasic'),
'NewBusinessBasic'[Policy_StartDate] <= EARLIER('NewBusinessBasic'[Policy_StartDate])
)
)
or
AnnualPremiumCum =
CALCULATE(
SUM('NewBusinessBasic'[AnnualPremium]),
FILTER(
'NewBusinessBasic',
'NewBusinessBasic'[Policy_StartDate] <= EARLIER('NewBusinessBasic'[Policy_StartDate])
)
)
(I wrote it by hand. There might be some grammar mistakes)
Regards,
Lionel
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |