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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ANKITBISANI
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
Phil_Seamark
Employee
Employee

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
Phil_Seamark
Employee
Employee

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!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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