Forum Discussion
Calculated Column to Measure
Hello AllanBerces
To make the formula work when the [used_Area] column is in context, you can use the following DAX instead:
Overall_Cum_PT_YTD_Measure = VAR CurrentDate = SELECTEDVALUE ( TRS[WeekNo:] ) VAR Filteredtable = FILTER ( ALL ( 'TRS' ), TRS[WeekNo:] <= CurrentDate && TRS[Type] <> "Non-productive" ) RETURN IF ( ISINSCOPE ( TRS[used_AREA] ), CALCULATE ( SUM ( 'TRS'[NPTHrs] ), Filteredtable, VALUES ( TRS[used_AREA] ) ), CALCULATE ( SUM ( 'TRS'[NPTHrs] ), Filteredtable ) )I am attaching the Power BI with modified formula in the attachment.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Proud to be a Super User
Hello AllanBerces
Please use the following DAX to create the measure with the same logic that you have used for creating the calculated column.
Overall_Cum_PT_YTD_Measure =
VAR CurrentDate = SELECTEDVALUE(TRS[WeekNo:])
VAR Filteredtable = FILTER(
ALL('TRS'),
TRS[WeekNo:] <= CurrentDate
&& TRS[Type] <> "Non-productive"
)
RETURN
CALCULATE(
SUM('TRS'[NPTHrs]),
Filteredtable
)
Additionally, there is a BSPTA calculated column in your TRS that is erroneous. Make sure to fix it or delete it, as it might create issues testing other aspects.
I am also attaching the Power BI file for your reference.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Proud to be a Super User
Hi quantumudit thank yu very much for the reply, but when i put table show same value for used_AREA, possible to segregate by used_AREA
Thank you
Hello AllanBerces
To make the formula work when the [used_Area] column is in context, you can use the following DAX instead:
Overall_Cum_PT_YTD_Measure = VAR CurrentDate = SELECTEDVALUE ( TRS[WeekNo:] ) VAR Filteredtable = FILTER ( ALL ( 'TRS' ), TRS[WeekNo:] <= CurrentDate && TRS[Type] <> "Non-productive" ) RETURN IF ( ISINSCOPE ( TRS[used_AREA] ), CALCULATE ( SUM ( 'TRS'[NPTHrs] ), Filteredtable, VALUES ( TRS[used_AREA] ) ), CALCULATE ( SUM ( 'TRS'[NPTHrs] ), Filteredtable ) )I am attaching the Power BI with modified formula in the attachment.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Proud to be a Super UserHi quantumudit thank you very much working perfectly