DAX function for calculations meeting certain conditions

[xxyb720]

The calculation I want to do is count(if(group = A))/total count of group - count(if(group = C))/total count of group by region or by state depending on business needs. I am creating a new column in the visual so I can calculate the result based on different dimensions.

Here's what the original data looks like:

group	region	state
A	1	CA
A	1	CA
B	1	CA
C	1	CA

The desired result looks like this:

By region:

region	result
1	25%

* 25% = count of A/total count of group - count of C/total count of group

By state:

state	result
CA	25%

I've been researching around but didn't see anything that's close to what I need. Please help with the DAX function. Thanks in advance!

[v-henryk-mstf · Accepted solution]

Hi @xxyb720 ,

According to your description, you can refer to the following test results:

Count all =
VAR Count_A =
    CALCULATE ( COUNTROWS ( 'Date' ), 'Date'[group] = "A" )
VAR Count_C =
    CALCULATE ( COUNTROWS ( 'Date' ), 'Date'[group] = "C" )
RETURN
    DIVIDE ( ( Count_A - Count_C ), COUNTROWS ( 'Date' ) )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[Ashish_Mathur]

Hi,

Write these measures:

Count all = countrows(Data)

Count of A = calculate([Count all],Data[Group]="A")

Count of C = calculate([Count all],Data[Group]="C")

Ratio = divide(([Count of A]-[Count of C]),[count all])