Forum Discussion
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.
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)
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
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.
2. Create seven measures and place them in a table visual.
STATUS1 Measure = CALCULATE(DISTINCTCOUNT(DATA[ITEM]),FILTER(DATA,DATA[STATUS1]=MAX('Table'[State])))+0If 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.
9 Replies
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.
2. Create seven measures and place them in a table visual.
STATUS1 Measure = CALCULATE(DISTINCTCOUNT(DATA[ITEM]),FILTER(DATA,DATA[STATUS1]=MAX('Table'[State])))+0If 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.
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
Thanks for your reply. The above mentioned link not simpler to my case. Can you please advise regarding the summarise by column and count?
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.
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!
Hi,
Would you be willing to live with a report structure like this?
