Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello Community,
I am a new PowerBI user. I have a table visualization with fields from three tables. I have verified that the results displayed are correct. I now want to count the number of entries, and do a sum on the currency field. When I try to use the sum function on the currency field; it will add all of the values in the table, not just the values in the visual. The same thing happens when I try to do a count. Any ideas why this is happening?
Sincerely,
Peter
Solved! Go to Solution.
Hi @Anonymous ,
If i understand you correctly, ALLSELECT() function might be helpful for you.
Please refer to the measures below and modify your measures.
sum = CALCULATE(SUM('Table'[PotentialValue]),ALLSELECTED('Table'))
count = CALCULATE(COUNT('Table'[ID]),ALLSELECTED('Table'))
Result would be shown as below.
BTW, since the filters you used are visual level filter, they can't affect other views such as SUM and COUNT above.
Instead, you can use page level filter if you want.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If i understand you correctly, ALLSELECT() function might be helpful for you.
Please refer to the measures below and modify your measures.
sum = CALCULATE(SUM('Table'[PotentialValue]),ALLSELECTED('Table'))
count = CALCULATE(COUNT('Table'[ID]),ALLSELECTED('Table'))
Result would be shown as below.
BTW, since the filters you used are visual level filter, they can't affect other views such as SUM and COUNT above.
Instead, you can use page level filter if you want.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
are you doing it as measure and inside the "calculate" container?
for example
Measure = calculate(sum([currency]))
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hello,
Thank you for the quick reply. I am using the desktop client, to create a report and have imported the three tables. As I create the report, with the visual highlighted: I go to the fields list and select the 'down' ceveron and select the summary function I want to use (count on the ID field, or sum on the AnnualSales field.
Sincerely,
Peter
@Anonymous
try go to the Fileds pane, right click to you table and New Measure
Measure = calculate(sum([currency]))
then add your Measure to the visual and set Do not aggregate in Vizualization pane
this technique is enable you a "row-context"and should give you a value in accordance with other column in the each row
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi,
I thought this would help in the explination. Below is an image of my table visualization while in Report view on the desktop client. I want to count the number of values in the Handoff column, and sum the values in the PotentialValue column.
So I want to see the number 7 for the count, and 150,000 for the value.
Thanks,
Peter
@Anonymous
create 2 new measures
1.
MeasureCount = calculate(count([Handoff ]);ALL('Table'))
and
MeasureSum = calculate(sum([PotentialValue]);ALL('table'))
then add this measures to table visual and set Do not aggregate
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi,
I tried your suggestion. I was unable to use the syntax you provided in its entirety. The ;ALL('Table') did not work for me. I did create the measure in the image below and added it to my table.
It qadded a new column with the number 1 for each row and added each row to my results, thus I have a total of 1741 rather than the 7 I need. What I would like to do is just be able to count the returned results. Using my prior image, I would like the Total to appear at the bottom with the number 7 under Handoff and 125,000 under the Potental Value column.
Peter
@Anonymous
what do you mean "The ;ALL('Table') did not work for me"?what kind error do you see?try to replace ";"to ",". it depends on localization
and why count should be 7 if i see scrollbar and 8 ros as minimum in visual
you could share your pbix-file to https://uploadfiles.io/ for example
do not hesitate to give a kudo to useful posts and mark solutions as solution
User | Count |
---|---|
77 | |
74 | |
42 | |
32 | |
28 |
User | Count |
---|---|
100 | |
93 | |
52 | |
50 | |
48 |