Counting categories that are created by a measure

[AFI]

Hi

I’m new to Power BI and I’m trying to get my head around counting categories that are created by a measure. I have a matrix that has a number of measures, many of which are used to create a category field for error flags. The field is basically a red flag indicator that something is not right based on the measure results (actually just comparing submitted budgets and actual results in two different dimensions – if they’re exactly the same they’re likely to not be reliable).

The measure that produces the red flag categories is this:

Unreliability Status = if(isblank([ExpPEFAVar]),BLANK(),if(AND([ABS B2A GFS Var]=0,[ExpPEFAVar]=0),"Highly Unreliable",if(OR([ABS B2A GFS Var]=0,[ExpPEFAVar]=0),"Unreliable","Pass Test 1")))

The main matrix is here:

What I want to do is count all the categories that are produced by the Unreliability Status measure. As a minimum I just want to count the number of “highly unreliable”, “unreliable” and “Pass Test 1” results. I’ve tried various means including variables.

Grateful for any ideas how to do this.

Cheers

[v-janeyg-msft · Accepted solution]

Hi, @AFI 

It’s my pleasure to answer for you.

According to your description,I think you can use a variable in the measure to store a summraize table composed of rows and columns of your matrix, and then add  'addcolumn' which is similar to the logic of your 'Unreliability Status' measure in this measure, and then calculate the number of corresponding categories on this summraize table.

If it doesn’t solve your problem,Could you please share some sample data for better to solve your problem? Please feel free to ask me.

Best Regards

Janey Guo

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

[AFI]

That works - using summarize to create another table then use count on that table to produce the visualizations. I got it working for first flag just usinng new columns on each step to get the right variance of total expenditure to budget. Variance of composition is a bit more difficult. But the single flag is fine for dashboard updates. More detailed analysis can easily in excel. 

[littlemojopuppy]

Probably easiest using table variables and counting rows from there.  Can you peovide some sample data?