Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
AleksandrMe
Resolver I
Resolver I

Visualization Graphs/Average and Labels

Good day Community, 

 

I have prbably simple question. I have data which looks like that. Data comes from similar Excel files from different assessments:

                       

                   ITEM 1                               ITEM 2                    ITEM 2

Manager      GOOD                               BAD                    NOT ASSESSED

Operator   NOT ASSESSED                 VERY GOOD               BAD

Assistant       GOOD                       AVERAGE                      NOT ASSESSED   

 

In a background of Excel, I have chaged asessemnt crtieria to NUMBERS to allow better visualization for AREA CHARTS etc... Text can only be presented by PIE CHARTS in %.  

 

GOOD is 5

AVERAGE is 3 

NOT ASSESED is 0 

 

Question #1: In need average mark for every  item, how to exclude 0 mark from average. I do not want to hide 0 as I want to know how many times "NOT ASSESSED" was used. 

 

Question#2: On are chart, It will preferlty show what I need if I use numbers - Can I change legend and labels in Power Bi to customize numbers back to TEXT for better understanding of reader? 

 

Thnaks

Aleks 

 

 

 

1 ACCEPTED SOLUTION

@AleksandrMe 
For Q2, you may create another column as the labels, something like:

 

Column 2 = SWITCH(TRUE(),[Measure]<3,"Don't Meet Standard",[Measure]>=3,"Meet Standard")

 

Paul Zheng _ Community Support Team
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
Greg_Deckler
Super User
Super User

@AleksandrMe - I would unpivot those last three columns (ITEM1, ITEM2, ITEM3) in Power Query. You could get the average of no 0's by doing something like:

You could create a column then like this:

Column = SWITCH([Value],"GOOD",5,"AVERAGE",3,"NOT ASSESSED",0)

Then a measure like this:

Measure = AVERAGEX(FILTER('Table',[Column]<>0),[Column])


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  - Thanks Greg. 

 

I figured out how to deal with average exlusion of ) values. However still not able to figure out if my another idea is possible at all. 

 

Please see screen - TO have this graph I need Numbers but Not Text. I woul like to keep this type of graph by I woul like to use text for labels - this is where I struggle. Reader needs to undertand what 3.0 means, this for example "Meet Standard" etc...

 

AleksandrMe_0-1599740921619.png

 

@AleksandrMe 
For Q2, you may create another column as the labels, something like:

 

Column 2 = SWITCH(TRUE(),[Measure]<3,"Don't Meet Standard",[Measure]>=3,"Meet Standard")

 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors