March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi,
I would like to create a bar chart but havent found an easy way yet to do so. I have no problem when all the data is within one column , but sometime I need to summarize a question with data in multiple column.
My data has one column for each possible answer. As below :
I
I need to build one bar chart graph summarizing the results of the data for "Which Option?" that would look like that
I have tried many things but fail as my data is structured that way.
Can someone help ?
Solved! Go to Solution.
You can do this with both DAX and Power Query
With DAX....... Go to Modelling Tab and press the NEW TABLE button...
NEW TABLE = VAR TotalRows = COUNTROWS ( TableName ) RETURN UNION ( ROW ( "Option", "Option 1", "Count", DIVIDE ( CALCULATE ( COUNTROWS ( TableName ), TableName[Option 1: Which Option?] = "Option 1" ), TotalRows ) ), ROW ( "Option", "Option 2", "Count", DIVIDE ( CALCULATE ( COUNTROWS ( TableName ), TableName[Option 2: Which Option?] = "Option 2" ), TotalRows ) ), ROW ( "Option", "Option 3", "Count", DIVIDE ( CALCULATE ( COUNTROWS ( TableName ), TableName[Option 3: Which Option?] = "Option 3" ), TotalRows ) ) )
Hello,
For this issue i recommend creating a measure for each option ( as long as it's about three in your real data aswell )
Use the COUNT() function to count the times option 1 is used in column 1 + the times option 1 is used in column 2 etc...
Do this for every option and add the measures to your bar chart.
Hope this helps
You can do this with both DAX and Power Query
With DAX....... Go to Modelling Tab and press the NEW TABLE button...
NEW TABLE = VAR TotalRows = COUNTROWS ( TableName ) RETURN UNION ( ROW ( "Option", "Option 1", "Count", DIVIDE ( CALCULATE ( COUNTROWS ( TableName ), TableName[Option 1: Which Option?] = "Option 1" ), TotalRows ) ), ROW ( "Option", "Option 2", "Count", DIVIDE ( CALCULATE ( COUNTROWS ( TableName ), TableName[Option 2: Which Option?] = "Option 2" ), TotalRows ) ), ROW ( "Option", "Option 3", "Count", DIVIDE ( CALCULATE ( COUNTROWS ( TableName ), TableName[Option 3: Which Option?] = "Option 3" ), TotalRows ) ) )
With Power Query, its much more convenient if you have many columns
Step # 1
Unpivot your Table by selecting all Columns. Rename the Columns
Step # 2
Add this MEASURE to the TRANSFORMED table
Percentage = DIVIDE ( CALCULATE ( COUNTROWS ( TableName ), FILTER ( ALLEXCEPT ( TableName, TableName[Which Option] ), TableName[Value] <> "null" ) ), CALCULATE ( COUNTROWS ( TableName ), ALLEXCEPT ( TableName, TableName[Which Option] ) ) )
Hi,
Above approach of creating a seperate table will work one way, i.e. if the slicer/filters are from master data, data in new table can be sliced accordingly, whereas if we click on newly created response (from new table), it will not bring any change in charts created from master data.
Can that be resolved.
Regards,
Parth
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |