Subtotal Matrix Percentages by Another Total

[MurphyYancey]

Hi everyone! Relatively new to PowerBi and this task has me stumped. I'm trying to create a percentage column that gets the percentage of each Sub account by the Main Account. I don't even know if this is possible. I've been able to get the percentage if the Account and Subaccount are the same, but when it comes to the Subaccounts with different Accounts, they return blank. 

For this specifically, I need to get the percentage of New Cat Machines (Subaccount) from Sales (Account). 

Does anyone have any ideas on how to do this? I am completely stumped.  

[v-karpurapud]

Hi @MurphyYancey 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @MurphyYancey 

We have not received a response from you regarding the query and were following up to check if you have found a resolution from the information provided below. If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank You!

[MurphyYancey]

@techies @v-karpurapud I appreciate both replies! I'm confirming with my team that I can get a sample dataset. Both responses return 1.0 for the percentages where I need something a little different. Until I can get a dataset, here is a screenshot of what I am more looking for (using Excel to show): 

[v-karpurapud · Accepted solution]

Hi @MurphyYancey 

Please try the below DAX:

% of Sales = 
DIVIDE(
    SUM('Table1'[Amount]),
    CALCULATE(
        SUM('Table1'[Amount]),
        ALLSELECTED('Table1'[GL Account], 'Table1'[Account Description]),
        'Table1'[Account] = "Sales"
    )
)

I hope this meets your requirement. If this doesn’t fully solve your issue, please share a sample of your data and more context so we can provide a more precise solution.

If this post helps, kindly mark it as Accepted Solution.

Thank You!

[v-karpurapud]

Hi @MurphyYancey 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @MurphyYancey 
Thank you for reaching out to the Microsoft Fabric Community Forum.
 

Regarding your query on creating a subtotal Matrix Percentages by Another Total , while I’m not fully aware of the exact structure of your dataset, I’ve created a sample .pbix file to demonstrate one possible solution.

Try the below DAX:

% of Sales = 
DIVIDE(
    SUM('Table'[Amount]),
    CALCULATE(
        SUM('Table'[Amount]),
        'Table'[Account] = "Sales"
    )
)

 
I’ve attached a screenshot and the .pbix file demonstrating this logic. Please review them to see if this solution aligns with your requirements.

If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution.

If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

Thank You!

[techies]

Hi @MurphyYancey something like this

% of Main Account Total =

DIVIDE(

    SUM('Sheet1'[Value]),

    CALCULATE(

        SUM('Sheet1'[Value]),

        ALLEXCEPT('Sheet1', 'Sheet1'[Account])

    )

)