Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
yenteng
New Member

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

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Super User
Super User

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])

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.