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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.