Forum Discussion

ppgandhi11's avatar
ppgandhi11
Helper V
7 years ago
Solved

DAX Formula help - conditional calculation

Hi, I have the first 2 columns, I want to computer the 3rd column based on the logic given in notes.   ServiceCount is not a column. It is a measure that I have calculated correctly based on some ca...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi ppgandhi11

     

    You may refer to below measure:

    Measure  =
IF (
    MAX ( Table[Service] ) = "D01"
        || MAX ( Table[Service] ) = "D02",
    [ServiceCount]
        / CALCULATE (
            [ServiceCount],
            ALL ( Table[Service] ),
            Table[Service] IN { "D01", "D02" }
        )
)

     

    Regards,

    Cherie 

ppgandhi11's avatar
ppgandhi11
Helper V
7 years ago

I am able to get to below working which is not a complete solution, but it is first step in solution I believe.

 

ServiceLevelCompare = DIVIDE(Master[ServiceVolume-Provider], CALCULATE(count(Master[Line Item Number]),FILTER(ALLSELECTED(Master[Service]),Master[Service] = "D01" || Master[Service] = "D01")))

 

count(Master[Line Item Number] is basically sum of ServiceCount of D01 and D02 (100 + 200).

 

Master[ServiceVolume-Provider] is the numerator (100 for D01, 200 for D02).

 

Problem is: this is working only for D01 and D02. It produces correct result only for D01 and D02. For all others, it does not produce right result. it still divides for all rows with 100+200, which is incorrect.

 

I am unable to say that, For D01 - do this. For D02 - do this. For D03 - do something else.

 

I am still fairly new to power bi. So I am not sure how to get this done. Thanks.

ppgandhi11's avatar
ppgandhi11
Helper V
7 years ago

Current output I am getting is with above formula:

 

 

 

Somehow I need to do the conditional calculation as shown in original post. Something like:

 

If(master[Service] = "D01" || master[Service] = "D02", <Do ServiceCount sum for D01 and D02>, N/A)  etc.

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi ppgandhi11

     

    You may refer to below measure:

    Measure  =
IF (
    MAX ( Table[Service] ) = "D01"
        || MAX ( Table[Service] ) = "D02",
    [ServiceCount]
        / CALCULATE (
            [ServiceCount],
            ALL ( Table[Service] ),
            Table[Service] IN { "D01", "D02" }
        )
)

     

    Regards,

    Cherie 

    • ppgandhi11's avatar
      ppgandhi11
      Helper V
      7 years ago

      Cherie,

       

      Thanks a lot. Your solution works. This is exactly what I was looking for. Thanks again!

       

      Prashant-