Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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') ) )
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') ) )
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |