How to create a measure with IF function that the logic is based on a merged column?

[yenteng]

Hi Fabric Community,

I would like to create a measure with the IF function, whereby

Premium per agent = IF(Data[Merged yyyymmm]="2024 Jan", DIVIDE(SUM(Data[premium amount]),3), DIVIDE(SUM(Data[premium amount]),DISTINCTCOUNTNOBLANK(Data[Merged Alias]).

*Merged yyyymmm is a merged column I created via transform data, and it consist of year and month, e.g. 2024 Jan, 2024 Feb, 2024 Mar etc

*Merged alias is a merged column created via transform data, which consist of the name of agents.

I encounter a problem now whereby I have no idea how to create the measure in PowerBI, especially the first logic part whereby I could not get Data[Merged yyyymmm] when I tried to enter this in the formula bar. 

My ultimate goal is to calculate, if the data belongs to Jan 2024, then will need to divide the premium amount by 3 agents only (in fact, there are more agents in this month but we only want to divide by the 3 significant agents), while if the data is not Jan 2024, then we will just use the usual calculation which is divide premium amount by all agents during that month.

[v-zhengdxu-msft · Accepted solution]

Hi @yenteng 

Thanks for the reply from @Gabry , please allow me to provide another insight:

You can use the SELECTEDVALUE() function to get the current value in the table visual in the report view, this may help you:

Measure = 
IF (
    SELECTEDVALUE (Data[Merged yyyymmm]) = "2024 Jan",
    DIVIDE ( SUM ( Data[premium amount] ), 3 ),
    DIVIDE (
        SUM ( Data[premium amount] ),
        DISTINCTCOUNTNOBLANK ( Data[Merged Alias] )
    )
)

Best Regards

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

[Gabry]

Hello,

you don't need to use a merged column with yyyymm. You can use your regular date column with date data type.

You can use the function datesbetween...so your formula should look like this

test = IF(SELECTEDVALUE('Table'[Date]) in DATESBETWEEN('Table'[Date], DATE(2024,01,01),DATE(2024,01,31)), CALCULATE(DIVIDE(SUM('Table'[number]),3)),DIVIDE(SUM(Data[premium amount]),DISTINCTCOUNTNOBLANK(Data[Merged Alias])