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

Calculating the percentage after the filtering in case of percentage does not adding upto 100 %

 

hello Experts ,

 

I have a below scenario with multiple choice questions for the survey . 

 

Need to plot a bar graph with Reason (Artistic,cheap,easily)  with percentage of total value.

 

IdReason 1reason 2reason 3value
1Artisticcheap 1
2 cheapArtistic2
3easily available   1

 

Below is my approch in Power BI with Unpivot:

 

idReasonvalue%(value/9)
1Artistic111.11111111
1cheap111.11111111
1easily  111.11111111
2cheap222.22222222
2Artistic222.22222222
3easily available 333.33333333
  Total111.1111111

 

But when I try to filter with Zone as East  , (Dimetion table as below with ID , Zone ,City ), I want to calculate the value as below. There has been 1: m relationship between dimention table and the fact one. The ID's in the dimention table with East zone is filtered in the below fact table.

 

IDREASONValue%(value/5)
1Artistic120
1cheap120
1easily  120
3easily available 360
  Total

120

 

 Dimention 
IDZONECITY
1EASTA
2WESTB
3EASTC

 

But it gives the value as below screenshot  .Baiscall it is calculating the percentage and then filtering. I want to have filter first and then calculating.

 

IDREASONValue%(value/9)
1Artistic111.11111111
1cheap111.11111111
1easily  111.11111111
3easily available 333.33333333
  Total66.66666667

 

Kindly let me know whether the approch is right . As it works w/o filter but with filer it is not giving up the desired result.

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @ANKITBISANI

 

I think you have done the right thing to unpivot the data using Power Query, but don't worry about calculating the percentages in this case in Power Query,

 

Once you return the data to Power BI, you can add calulcated measures to your table like the simple one below which will calculate the percentage on the fly AFTER the filters have been set.

 

The measure can be tweaked to ignore/consider specific filters depending on need.

 

Let us know what you think 🙂

 

Measure = DIVIDE(
				CALCULATE(SUM('Table1'[value])),
				CALCULATE(
						SUM(Table1[value]),
						ALL('Table1')
					)
				)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Microsoft
Microsoft

Hi @ANKITBISANI

 

I think you have done the right thing to unpivot the data using Power Query, but don't worry about calculating the percentages in this case in Power Query,

 

Once you return the data to Power BI, you can add calulcated measures to your table like the simple one below which will calculate the percentage on the fly AFTER the filters have been set.

 

The measure can be tweaked to ignore/consider specific filters depending on need.

 

Let us know what you think 🙂

 

Measure = DIVIDE(
				CALCULATE(SUM('Table1'[value])),
				CALCULATE(
						SUM(Table1[value]),
						ALL('Table1')
					)
				)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

HI @Phil_Seamark ,

 

Thank you for the reply.

 

I have many filters (about 10-15) and that is driven by the dimention table with ID as a unique column.

(Those dimention table with zone ,city in as exampe).

 

I have put those column into my slicer and then they drive the other fact tables with ID as 1: M relationship.

 

As I cant add upto 100 % , i cant use on the fly calculation as case is being the multiple choice question.

 

So I would want every percentage disintegrated upto the lowest level i.e ID. That is why I have broken percentage into individual ID with (value/total value) in the custom column.

 

Now , when I filter , I am not getting the desired result.i want to have the filter first and then calculate custom column.

 

Is it possible that I filter first. Beasue that would mean chnaging the dataset rows dynamically. ( Table would contain only rows which have that filters and then calculate (value/total value))

 

i want to have bar chart which would get drive from the selected filters .

 

Agreed with you I can go with measure but how would I be able to calcualte the measures for so many filters that too on ID level.

 

Regards

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors