cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JPY Regular Visitor
Regular Visitor

The totals are inaccurate with Sample data attachted

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

 

Capture4.PNGCapture5.PNG

 

Hi @vanguripavan

Kindly pls see the sample data.

 

Thanks for help

Joyce

1 ACCEPTED SOLUTION

Accepted Solutions
natelpeterson New Contributor
New Contributor

Re: The totals are inaccurate with Sample data attachted

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

7 REPLIES 7
natelpeterson New Contributor
New Contributor

Re: The totals are inaccurate with Sample data attachted

@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 Regular Visitor
Regular Visitor

Re: The totals are inaccurate with Sample data attachted

Hi @natelpeterson ,

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 Regular Visitor
Regular Visitor

Re: The totals are inaccurate with Sample data attachted

Hi @natelpeterson ,

How to get this number?

 

Capture7.PNG

Thanks,

Joyce

natelpeterson New Contributor
New Contributor

Re: The totals are inaccurate with Sample data attachted

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

Community Support Team
Community Support Team

Re: The totals are inaccurate with Sample data attachted

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JPY Regular Visitor
Regular Visitor

Re: The totals are inaccurate with Sample data attachted

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

JPY Regular Visitor
Regular Visitor

Re: The totals are inaccurate with Sample data attachted

Hi @natelpeterson,

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

Thank you so much. Smiley Happy