Column Totals calculating total Sum, not the sum of the column's values

[quincy_p]

Hello - I am having a tough time getting a power BI matrix to relfect the correct totals in the columns at the 1st row level:

I would expect that the totals at the top would count the entire columns values not the value of the entire first row.

In this I have 3 tables at work: Parent_PM, SVMX Contracts, Contract_Data

As is shown I need to be able to have a matrix that will show the following fields:

'SVMX Contracts' [Contract_Engineer], SVMX Contracts' [Name], 'PM_Parent' [Scheduled_Date_Time__c] and then a sum of COntracts_data [IB_ITEM_INSTANCE] per Name and then obviously per the scheduled date.

I tried to make a column in SVMX Contracts like so but that did not work.

Asset_Count = 
CALCULATE(
    COUNTROWS(
        FILTER(
            Contract_Data, 
            NOT(ISBLANK(Contract_Data[SERIAL_NUMBER])) &&
            Contract_Data[Contract_Connection] = RELATED('SVMX Contracts'[Connector])
        )
    )
)

Any ideas? I cannotpull the date into the SVMX Contracts as this will create duplicates - i ntheory each contract can have multiple serice dates within the year so this needs to be reflected.

Thanks!

[v-karpurapud]

Hi @quincy_p 

Could you please confirm if your query have been resolved the solution provided by @Anonymous  ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[Anonymous · Accepted solution]

Hi, @quincy_p 
 

Thanks for reaching out to the Microsoft fabric community forum.

1.Based on your requirements, I suggest a simpler setup:

This way, you can have the first row as the total.

2.Secondly, if you want the first row to output the same total value, you can also try using the isinscope function to determine the expected result for the current row.

Here is the relevant documentation:

ISINSCOPE function (DAX) - DAX | Microsoft Learn
HASONEVALUE function (DAX) - DAX | Microsoft Learn
Hierarchies – DAX Patterns
 

3.Finally, if you need us to regenerate the suggestions based on your data structure, please remove the data and share your sample data with us.As mentioned earlier, I am not certain if this solution will address your issue.We recommend uploading it to GitHub and sharing the link with us.When uploading a file, please be careful to delete sensitive information.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Leroy Lu

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