cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ppgandhi11
Helper V
Helper V

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 calculation.

 

ServiceCount = count(LineItem)

 

Basically I have been trying something like:

 

ServiceLevelCompare = if(Service = "D01" || Service = "D02",

calculate(sum(ServiceCount), filter(master, Service in {"D01","D02"})),

if(Service = "D04" || Service = "D05",

calculate(sum(ServiceCount), filter(master, Service in {"D04","D05"})), "N/A"))

 

Above is only intended to get the sum part (denominator) in the calculation. Once we have that, the calc will need to just use divide function with numerator being ServiceCount.

No luck so far!  Thanks a lot!

 

Capture.PNG

1 ACCEPTED SOLUTION

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 

Community Support Team _ Cherie Chen
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

6 REPLIES 6
arvindsingh802
Solution Sage
Solution Sage

I think this will help

 

ServiceLevelCompare = DIVIDE(ServiceData[ServiceCount],IF(ServiceData[Service] = "D01" || ServiceData[Service] = "D02",
CALCULATE(SUM(ServiceData[ServiceCount]),FILTER(ALL(ServiceData),ServiceData[Service]="D01"||ServiceData[Service]="D02")),

IF(ServiceData[Service] = "D04" || ServiceData[Service] = "D05",
CALCULATE(SUM(ServiceData[ServiceCount]),FILTER(ALL(ServiceData),ServiceData[Service]="D04"||ServiceData[Service]="D05")), 1)),0)

 

Regards,

Arvind

Thanks for the reply Arvind.  This does not compile. There is a syntax error in the beginning itself around the first IF condition.

 

... IF(ServiceData[Service] = "D01"  <-- this does not compile. The Service is not a measure it says.  The only available options are the measures of the context.

 

Have you tested it at your end by any chance?   

 

Thanks again.

 

Prashant-

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.

Current output I am getting is with above formula:

 

 

Capture.PNG

 

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.

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 

Community Support Team _ Cherie Chen
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

Cherie,

 

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

 

Prashant-

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors