cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nejovic
Helper I
Helper I

Addition Problem

Hi, 

I'll try to explain my problem with example.

This is my data:

data.PNG


I want to create visualization that will show the total number of 'As', 'Bs', 'Cs' and 'Ds' in my four columns. So the result should be: 

 

A - 5

B - 4

C - 2

D - 1

 

What I did was: I choosed Clustered Bar Chart > Insert Column 1 in Values > New Quick Measure > Addition > Added Colum 2 > New Quick Measure > Addition > Added Column 3 > New Quick Measure > Addition > Added Column 4. 

What I got is this:

Addition.PNG
As you can see, this is not the result I wanted. I got 6 Bs, 4 As, 2 Cs, and no Ds, which doesn't correspond to the real data. 

What should I do to get the correct result? 

I'd really appreciate your help. 

Thank you!

1 ACCEPTED SOLUTION
moumipanja
Microsoft
Microsoft

You might use Power Query  to reshape your table and then create a measure using DAX to show the count of occurences. Hope this solution helps.

1. Click on Edit Queries -> select Transform tab -> select all your columns and then click on unpivot columns

 

1.PNG

 

2. once done, you will get the following table

2.jpg

3. Click on Close and apply. Now use the following DAX to create a measure as below:

Measure Count = COUNTROWS(Sheet1)
 
4. Select clustered bar chart -> Value in Axis and Legend, Measure Count in Value
 
3.PNG

View solution in original post

5 REPLIES 5
moumipanja
Microsoft
Microsoft

You might use Power Query  to reshape your table and then create a measure using DAX to show the count of occurences. Hope this solution helps.

1. Click on Edit Queries -> select Transform tab -> select all your columns and then click on unpivot columns

 

1.PNG

 

2. once done, you will get the following table

2.jpg

3. Click on Close and apply. Now use the following DAX to create a measure as below:

Measure Count = COUNTROWS(Sheet1)
 
4. Select clustered bar chart -> Value in Axis and Legend, Measure Count in Value
 
3.PNG

View solution in original post

Thank you everyone! 
 

This method with unpivoting columns works if I only had these 4 columns. However, I have additional columns, and when I unpivot these columns, that disrupts my statistics for other variables. 

Just a thought. You can create a new table using those 4 columns, unpivot it and get the desired result. For rest of the calculations you can use your original table. Let me know if this works.

Vvelarde
Community Champion
Community Champion

@Nejovic

 

Hi, you can go to Edit Query, Select your columns and Apply an Unpivot Columns. Finally delete empty rows

 

Now with this, just apply a simple count in your visual

 

Regards

 

Victor

 




Lima - Peru
AClerk
Solution Sage
Solution Sage

HI @Nejovic

How about creating measures like --> 

COUNTA = COUNTROWS(FILTER(Table1,Table1[1] = "A")) + COUNTROWS(FILTER(Table1,Table1[2] = "A")) + COUNTROWS(FILTER(Table1,Table1[3] = "A")) + COUNTROWS(FILTER(Table1,Table1[4] = "A"))

And then showing a visual for all measures?
Is that applicable?

2019-01-29 09_52_28-Clipboard.png

 

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors