Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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.

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    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.

2 Replies

  • Gabry's avatar
    Gabry
    Icon for Super User rankSuper User
    2 years ago

    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])
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    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.