Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am calculating percentage of product values as below.
Prod % Value
A 5%
B 4%
C 10%
D 8%
E 9%
F 3%
G 11%
H 16%
I 18%
other 12%
But when I do Top 5 or exclude other, the percentage values shouldn't change, not sure how to get it done.
%Value is calculated as sum(value)/Total(Value)
Any thought.
Thanks,
Solved! Go to Solution.
@Anonymous
Have a look at the attached pbix file which shows the Top 5 applied on average.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
@Anonymous
Add a filter if you want to exclude product D
Percentage = SUM ( Table[value] ) / CALCULATE ( SUM ( Table[Value] ), All(Table[Prod]), FILTER('Table' , Table[Prod] <> "D") )
@Anonymous
A formual like the following may help you.
Percentage = SUM ( Table[value] ) / CALCULATE ( SUM ( Table[Value] ), ALL ( Table[Prod] ) )
Thanks for the reply, but how to restrict the product D in grand total.
Thanks,
@Anonymous
Add a filter if you want to exclude product D
Percentage = SUM ( Table[value] ) / CALCULATE ( SUM ( Table[Value] ), All(Table[Prod]), FILTER('Table' , Table[Prod] <> "D") )
The formula works well, but when I do Top 2 records my percentage values are changing. but in the example it works well, but in real data is getting changed a lot. not sure why??
@Anonymous
You should use SUMX and Values.
Try the following formula:
Percentage = SUMX ( VALUES( Table[Prod] ), CALCULATE ( SUM ( Table[value] ) ) ) / SUMX ( VALUES( Table[Prod] ), CALCULATE ( SUM ( Table[Value] ), All(Table[Prod]), FILTER('Table' , Table[Prod] <> "D") ) )
Hi @Anonymous
Can you elaborate a bit more?
Is what you're showing a matrix with Prod on rows and %Value (measure) in values?
What do you mean exactly by "when I do Top 5 or exclude other" ?
Thanks AIB for the response, basically above %values shouldn't change when we do Top 5 in filter section or excluing any other Prod lov's.
Any idea.
Hi @Anonymous
You can acheive this by using ALL() in your measure.
It will be easier to help if you can show some sample data table from which you are creating this report.
It is better to explain the issue like below example.
"My problem is to get the id number separated from a column which shows the data as below."
My actual data is in a column as below
Column1 |
Hello12Monday |
Hello14Tuesday |
The reuslt I need should be
Column1 | ID No |
Hello12Monday | 12 |
Hello14Tuesday | 14 |
This way you can explanin the issue clearly and instead of pasting the screenshots in the question please consider copying the excel table.
I hope this will lead solving the issue fast.
Regards
Affan
@Anonymous
Have a look at the attached pbix file which shows the Top 5 applied on average.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Hi Affan,
You are solution worked well, but in my scenario both numerator and denominator have many filters and it didn't worked as per the example which you share.
Any other thoughts?
Appreciated your support.
Thanks,
Thimma
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |