The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi all,
I am trying to create a measure to Dynamically calculate by row a value through year :
In my first table I have the value a & b
a is a decimal number
b is a integer
In a another table I have my value:
t is the year starting from 1 and go up to 6
the year can dynamically change and I am stuck in my DAx formula as I am using CALCULATE and SUM
Supply =
CALCULATE( CALCULATE(SUM(a) * POWER((1 - SUM(b),t))
In excel the formula is as Follow :
a * (1 - b ) ^ t
Hi @Fantmas
Please try
Supply =
VAR t =
SELECTEDVALUE ( Table2[Year] )
RETURN
SUMX (
Table1,
VAR a = Table1[ValueA]
VAR b = Table1[ValueB]
RETURN
a * ( 1 - b ) ^ t
)
To replicate this formula in DAX, you can try the following:
Supply =
CALCULATE(
SUM(a) * POWER(1 - SUM(b), t),
ALLSELECTED(table)
)
This will calculate the value of Supply using the values of a and b in the current context, and raise the result of 1 - SUM(b) to the power of t. The ALLSELECTED function is used to remove any filters on table, so that the full range of a and b values is used in the calculation.
If you want to use a specific year value for t instead of a dynamic value, you can replace the t column reference with a fixed value or another measure. For example:
Supply =
CALCULATE(
SUM(a) * POWER(1 - SUM(b), 3),
ALLSELECTED(table)
)
This would calculate the value of Supply using the values of a and b in the current context, and raise the result of 1 - SUM(b) to the power of 3.
I hope this helps! Let me know if you have any further questions.
Hi Matth,
Thank you for your answer, I tried your solution but it is not working, I should find a figures around 600 for my test but I find a figures around 2M,
My assumption it is POWER function is done after the sum function that create the issue
User | Count |
---|---|
26 | |
10 | |
8 | |
6 | |
6 |
User | Count |
---|---|
31 | |
11 | |
10 | |
10 | |
9 |