Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Subcategory Net sales

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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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