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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Saxon10
Post Prodigy
Post Prodigy

POWER BI visualizations - multiple columns with different status in same report

 

Hi,

 

I have one report that contain multiple columns (Status 1 to status 7) with different status (Okay, Not Okay, Completed and Not completed), each columns has different count with different status.

SOF1.PNG

 

Example; Status 1 has two different status which is "Okay" and "Not Okay", "Okay" count has 6039 and "Not okay" count has 13.

I summarised the data of each status for your reference. (Data prepared manually)

 

SOF2.PNG

At the moment I am using 7 different charts, is there any way I can achieve my result all of them in one chart? Can you please suggested the best way to achieve the result in Power BI visualizations? Herewith attached the excel file for your additional reference.

 

https://www.dropbox.com/s/6cmuh32sit97rr1/POWER%20BI-VISUAL.xlsx?dl=0

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

Hello @Saxon10 ,

Is the second screenshot the result of your desire?

If yes, we suggest that you create a new status table and seven measures. See the following steps.

1. Create a table with Enter Data.

Po1.jpg

2. Create seven measures and place them in a table visual.

STATUS1 Measure = 
CALCULATE(DISTINCTCOUNT(DATA[ITEM]),FILTER(DATA,DATA[STATUS1]=MAX('Table'[State])))+0

Po2.jpg

If you don't meet your requirements, could you show the exact expected result based on the table you shared?

Best regards

Community support team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it faster.

BTW, pbix as an attachment.

View solution in original post

9 REPLIES 9
v-zhenbw-msft
Community Support
Community Support

Hello @Saxon10 ,

Is the second screenshot the result of your desire?

If yes, we suggest that you create a new status table and seven measures. See the following steps.

1. Create a table with Enter Data.

Po1.jpg

2. Create seven measures and place them in a table visual.

STATUS1 Measure = 
CALCULATE(DISTINCTCOUNT(DATA[ITEM]),FILTER(DATA,DATA[STATUS1]=MAX('Table'[State])))+0

Po2.jpg

If you don't meet your requirements, could you show the exact expected result based on the table you shared?

Best regards

Community support team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it faster.

BTW, pbix as an attachment.

This solution worked except for status values containing space characters. 

In fact, it works on a small data sample but fails on large data feed.

 

Any suggestions?

 

2023-08-01 16_17_30-tas_windows-machine - Power BI Desktop.png

 

thanks for your time. This is exactly I am looking for.

Ashish_Mathur
Super User
Super User

Hi,

Would you be willing to live with a report structure like this?

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AllisonKennedy
Super User
Super User

I would try using the first table you posted - then in Power Query Editor select Status 1 through Status 7, click Home tab > Unpivot Columns. You will now have three columns: ID, Attribute and Value (you can rename these as you wish). 

 

Close and Apply the changes. 

 

Create a new measure: 

Distinct Count Items = DISTINCTCOUNT(Table[ID])

 

Then create a visual with [Distrinct Count Items] in Values, [Attribute] in Axis and [Value] in Legend.

 

Not sure if that will give you what you're looking for exactly, but you should be able to play around with the combinations from here.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi. Thanks for your reply and suggestions. Those 7 status came from DAX function measure so unable to use the power query editor options. 

@Saxon10 Please share what the raw data looks like, this will help us provide you with the best suggestions. You've probably seen it already, but Greg's post here is very helpful: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

If we don't know all the information, we can't give helpful responses. 

 

In addition to the raw data format of the tables, please share the DAX formulas you're using for the measures as well as any relationships between tables. 

 

Thanks!


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

amitchandak
Super User
Super User

@Saxon10 , I doubt this kind of output is possible.

There is one solution here: https://www.youtube.com/watch?v=WSnB5y2JXv8

Not sure that can be replicated in your case

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thanks for your reply. The above mentioned link not simpler to my case. Can you please advise regarding the summarise by column and count? 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.