The totals are inaccurate with Sample data attachted

[JPY]

Hi all,
I am wondering why the total values are inaccurate as shown below.

Please refer to the sample data provided in the link below.

Here is the link

Hi @Anonymous, 

Kindly pls see the sample data.

Thanks for help

Joyce

[Anonymous]

@JPY  - The problem appears to be that there are multiple Product Families associated with the same Ref Number. So, the Grand Total is an accurate representation, and each row is also an accurate representation of the amount associated with each Product Family. You generally want a fact table row to be associated with only one dimension table row.

Cheers!

Nathan

[JPY]

Hi @Anonymous ,

Thanks for your prompt reply.

Understand what you are saying, however how would I go about adopting your solution?

I simply want the total for each product family and removing duplicates may give inaccurate data.

Regards,

Joyce

[JPY]

Hi @Anonymous ,

How to get this number?

Thanks,

Joyce

[Anonymous · Accepted solution]

Hi @JPY ,

If that's the correct number, then you can create this new Calculated Table, which will not have a relationship to either table.

Summary PBIC = 
SUMMARIZECOLUMNS(
    'Margin PBIC'[Product Family],
    "Total Amount", 
    SUMX(
        RELATEDTABLE('Opp PBIC'),
        [Amount (converted)]
    )
)

Cheers!

Nathan

[JPY]

Hi @Anonymous,

I have tried the calculated table and it works for me.

Thank you so much. 🙂

[v-frfei-msft]

Hi @JPY ,

Where is the 10.7bn from? As I checked the pbix you shared. I cannot find the logic to get the correct result as you excepted.

[JPY]

Hi @v-frfei-msft ,

Thank you for your reply!

10.7 billion is the number that I get when I manually added up all the numbers.

Thanks,

Joyce