DAX for Power BI - True Total Sum - Row headers not equaling sum of

[Anonymous]

When trying to display totals following a calculation, i am unable to get column totals to match.

Source file

Finance.pbix 

Goal

Ultimately, i am trying to show 

• Parent company total
• Sub group totals
• broken down by Division
• Ability to slice and dice this data on graphics

Added to each page / report for cross reference

1. Supplier = parent supplier
2. Sub-group 2 = Supplier partnerships
3. % = % of supplier partnership
4. Group Total = Total the Supplier partnerships has invoiced
5. MP / OD / IT / Other = % of group total for the relavent divsion
6. Total = sum of MP / OD / IT / Other (equal to total Parent company has invoiced as part of supplier partnership)

Tables

I have 3 linked tables - 4th is a table used to summerize for an attempted solution

i have tried the following 3 solution

Attempt 1

Raw invoice by supplier, brings supplier group totals

Attempt 2

Rows match, column totals do not match.

Payment Measure 1 =
CALCULATE(
SUM('Invoices & Credits'[Payment Amount])
*
MIN(Supplier[%])
)

Attempt 3 (cyan)

Rows match, can substitue column total with a 4

Payment Measure 2 = 

IF(ISFILTERED(Supplier[Sub-Group-BI]),

    SUM('Invoices & Credits'[Payment Amount]) 
    * 
    MIN(Supplier[%]),
   4
   )
    
    

Attempt 3.1 (red)

Rows match, trying to substitue column total with formula returns save values.

Payment Measure 2.1 = 

IF(ISFILTERED(Supplier[Sub-Group-BI]),

    SUM('Invoices & Credits'[Payment Amount]) 
    * 
    MAX(Supplier[%]), 
    SUMX('Invoices & Credits', 'Invoices & Credits'[Payment Amount] * MIN(Supplier[%])
    )
    )

Attempt 4

Using summerrize table, correct overall totals i.e. supplier group 1, however whole row has same values

Summerize:

table = 
SUMMARIZE (
    'Supplier',Supplier[Sub-Group-BI],
    Supplier[Supplier-BI],
    'Supplier'[%], 
    "payment", SUM ( 'Invoices & Credits'[Payment Amount] ),
    "Total", MIN ( 'Supplier'[%]) * SUM ('Invoices & Credits'[Payment Amount] 
)
)

Measure

Payment Measure 3 = CALCULATE
     (SUM('table'
      [total]
     )
   )

Source file

Finance.pbix 

[parry2k · Accepted solution]

@Anonymous that's different, in your example tht was not the case, although what % will be used in case of many to many cardinality? Minimum or Maximum?? or you have to have parent supplier in your Invoice and credits table

[parry2k]

@Anonymous may be the simple would be to add new column with Payment Amount With % and that use that in the matrix for sum

Payment Amount with % = 'Invoices & Credits'[Payment Amount] * RELATED( Supplier[%] )

[Anonymous]

hi @parry2k 

Thank you for the response.

However I cant seem to get this to work, am i feel i am totally missing something

[parry2k]

@Anonymous are you getting error or what?

[Anonymous]

Hi @parry2k 

No errors, i just get the same results. I have a feeling im missing something glaringly obvious.

I have included a link to the trimmed source file on onedrive.

Source file

Finance.pbix 

[parry2k]

@Anonymous is this what you are looking for?

[Anonymous]

hi @parry2k 

Wow 👏

That is exactly what i was looking for, i could then mirror those values in various graphics.

How did you do that?

[parry2k]

@Anonymous file is attached, and added one column and one measure, starts with Parv

It is exactly what I suggested originally.

[Anonymous]

Hi @parry2k 

I knew it was something I would have been missing, I can see how you have got this to work on the data I have supplied thank you so much.

 It still hasn’t fully resolved the issue unfortunately  as I have many to many cardinality to get share the % of the supplier partnership.

Is there another way to get around this?

Supplier	Supplier Group	%
Parent Company 1	Supplier group 1	70%
Parent Company 2	Supplier group 1	30%
Parent Company 1	Supplier group 2	50%
Parent Company 3	Supplier group 2	50%

[parry2k · Accepted solution]

@Anonymous that's different, in your example tht was not the case, although what % will be used in case of many to many cardinality? Minimum or Maximum?? or you have to have parent supplier in your Invoice and credits table

[Anonymous · Accepted solution]

hi @parry2k 

Yes this covers it now, apoligies for my naivety with the many to many cardinality. Your solution covers it perfectly

Parv Payment 1 = SUMX ( VALUES ( Supplier ),
calculate(
    sum('Invoices & Credits'[Payment Amount])*
        min('Supplier'[%])
        
        )
)

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

Hi @Greg_Deckler

Thanks for the response.

I have been through the final word, scary as it was realising i may need to create more measure depending on the graphic.

however trying each of the M_single and M_totals i couldnt get it to work.

I notice this uses 1 table, where as i use 3 tables. Would i need to add further variables or conditions?

[Greg_Deckler]

@Anonymous  - Most times these things can be most easily resolved by you pasting some sample data, in this case perhaps a sample PBIX since you have 3 interrelated tables involved. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi @Greg_Deckler 

Thank you, i have included the link to the source file below. 

The post was very useful thank you so much 🙂

Source file

Finance.pbix