Exported Data with Wrong Values/Total

[Anonymous]

Hello everyone,

I have a 3 tables:

1 ClientList - ClientID

2 RetailStoreList - ClientID

3 Fact Table - ClientID

The Fact Table (3) is filtered by Client List (1) to bring only sum of revenue of the clients that are in the list. The RetailStore (2) filters the ClientList to give me the clients of any store and their total of revenue. The question I'm facing is:

When I creat a table with the Stores (table 2) and ClientsID (table 1) and Sum of Revenue (table 3) I have the right results when I export the data. But, when I remove the clientsID (table 1) the sum of revenue of any store in the excel data changes to wrong values. 

I don't know what is the cause. Could someone help me: Why the table is correct when I add the clientsID and wrong when I remove it?

Thanks a lot!

António.

[daXtreme]

Hi @Anonymous 

Such questions are not answerable because to really get to the back of the problem one has to know the model (all the relationships between the tables). You have not shown the model, so one can only roughly guess what's wrong. And it really does matter from which table you take your fields. The fact that 2 tables have the same fields does not mean that, if you grab one or the other, the figures will be the same. No, they won't be in general because formulas make some assumptions about which fields they work with. The rules of good data modeling dictate that fact tables should be hidden (you must not drag fields from them onto the canvas) and only dimensions should be exposed to the end user. If you stick to this and write your measures in accordance, you won't be scratching your head over such issues like this one.

[v-xiaosun-msft · Accepted solution]

Hi @Anonymous ,

If the visualization uses data from more than one data table, and no active relationship exists for those tables in the data model, Power BI only exports data for the first table.

According to your description, Table(3) is filtered by Table(1) and Table(2) filters Table(1). The relationships among three tables are active, so when you add the ClientID of Table(1), you can get the correct exported data.

But when you remove the ClientID (table 1), there is no active relationship exists between Table(2) and Table(3), and the “sum of revenue of the clients” is filtered by ClientID (Table1), leading to  the sum of revenue of any store in the excel data changing to wrong values. 

I suggest that you can establish the relationship between Table(2) and Table(3) or make the relationship between them active, and then try to export to data.

Please refer to the following documents.

Export data from a Power BI visualization - Power BI | Microsoft Docs

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.