Solved: Subcategory Net sales

Anonymous · ‎03-03-2022

Hi Power BI Users,

I am having Category1, category 2 and sales in my Table as below. I want net sales for category 2 excluding category. All dax is giving misccelaneous output. Can you guys help here.

Anonymous · ‎03-08-2022

Hi @Anonymous ,

You can create a measure as below to get the category Net sales, please find the details in the attachment.

Category Net sales = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[Category],
        "@ssales", CALCULATE ( SUM ( 'Table'[sales] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )
    )
RETURN
    SUMX ( _tab, [@ssales] )

Best Regards

View solution in original post

Anonymous · ‎03-08-2022

Hi @Anonymous ,

You can create a measure as below to get the category Net sales, please find the details in the attachment.

Category Net sales = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[Category],
        "@ssales", CALCULATE ( SUM ( 'Table'[sales] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )
    )
RETURN
    SUMX ( _tab, [@ssales] )

Best Regards

TheoC · ‎03-03-2022

Hi @Anonymous

You can calculate the subtotal by a category by using the following:

Category Sales = CALCULATE ( SUM ( 'Table'[Sales] ) , ALLEXCEPT ( 'Table'[Category] ) )

Hope this helps.

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias