cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AFI
Frequent Visitor

Counting categories that are created by a measure

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:

AFI_1-1606824759335.png

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

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
v-janeyg-msft
Community Support
Community Support

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.

View solution in original post

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
Super User I
Super User I

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.