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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KPro17
Frequent Visitor

Card not pulling data from SharePoint Excel tab

Good Day,

I have 4 excel documents that are saved in SharePoint. In each excel document it has a tab called Availability that allows the user to enter if they took a business, sick or vacation day during the a specific month. At the end of the each month it has 3 additional columns named Business, Sick and Vacation which deducts the total amount of days taken each (business, sick and vacation) and it shows the employee's balance days remaining for the year.

I created a card in Powerbi to show the balance days for each but it is pull a high figure (e.g. if the balance vacation days for user X is 40 it is pulling 591)

Is there a way just to pull the correct number from the columns suggested?BI_2.JPGBI_1.JPG

5 REPLIES 5
KPro17
Frequent Visitor

Any solutions how to resolve this. Thanks much.

KPro17
Frequent Visitor

Thanks for the feedback. I created the visual table but it is pulling duplicate data as seen. I only want to reflect the balance of each which is one row. Also, the bar charts and line chart is not reflecting accurate data. January suppose to be 63 days for vcation and its show 32 days for business and even sick which should be 4 and 14 respectively. 

KPro17
Frequent Visitor

fields used for business bar chartfields used for business bar chartcharts pulling 32 days for sick vacation and businesscharts pulling 32 days for sick vacation and business

KPro17
Frequent Visitor

Spoiler
Thanks much for the information provided. I created the visual table to show business, sick and vacation but it is coming up as two instances as seen in the picture. Also, i created some bar charts to show business, sick and vacation as well but it not reflecting the actual data example, Business leave should be 4 days but in the bar chart it is counting as 32 days. As well as Sick days suppose to be 14 days and its showing 32 days as well. Also, please see the image BI_2 which shows the configuration used.BI_2.JPG1 row of vac, sick, business should be seen in visual table, charts not displaying accurate data1 row of vac, sick, business should be seen in visual table, charts not displaying accurate data
v-jingzhang
Community Support
Community Support

Hi @KPro17 

 

From your screenshot, you are using Count to aggregate Business column. I recommend that you use Sum instead. You can right click on Business column in the Fields or Values box and select Sum. Just as below. 

vjingzhang_0-1647252800743.png

 

In addition, the Card visual is only able to display an aggregated value, so it will display the total of the whole Business column. If you want to show the value for every user, you can use Table visual. Put both user name and Business columns into a table visual, then you will see the total for every user. Or if you want to show the total in a Card visual for a user at a time, you can add a slicer to the report page and put User name column into it. Then use the slicer to filter users. 

 

Table visualizations in Power BI reports and dashboards - Power BI | Microsoft Docs

Slicers in Power BI - Power BI | Microsoft Docs

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors