cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## 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.

 Id Reason 1 reason 2 reason 3 value 1 Artistic cheap 1 2 cheap Artistic 2 3 easily available 1

Below is my approch in Power BI with Unpivot:

 id Reason value %(value/9) 1 Artistic 1 11.11111111 1 cheap 1 11.11111111 1 easily 1 11.11111111 2 cheap 2 22.22222222 2 Artistic 2 22.22222222 3 easily available 3 33.33333333 Total 111.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.

 ID REASON Value %(value/5) 1 Artistic 1 20 1 cheap 1 20 1 easily 1 20 3 easily available 3 60 Total 120

 Dimention ID ZONE CITY 1 EAST A 2 WEST B 3 EAST C

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.

 ID REASON Value %(value/9) 1 Artistic 1 11.11111111 1 cheap 1 11.11111111 1 easily 1 11.11111111 3 easily available 3 33.33333333 Total 66.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
Employee

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')
)
)```

Proud to be a Datanaut!

2 REPLIES 2
Employee

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')
)
)```

Proud to be a Datanaut!

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors