cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Microsoft
Microsoft

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
Microsoft
Microsoft

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. 

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors