Forum Discussion

lyrk8888's avatar
lyrk8888
Regular Visitor
4 years ago
Solved

Running Total By Product

I need help with Running Totals by Product in Power BI using DAX.  

 

Please see attached Example data. There are 3 levels in this Example:  1st Level is Store, 2nd Level is the Product, 3rd Level is the Sub Category.     

 

The Running total will need to be calculated only at the Product Level (2nd level).  If the row is a Store (1st Level) , the Running total will just be the Total from the Amount.  If the row is a Sub Product (3rd), the Running Total will need to be 0. 

 

StoreAmtSequenceRT - On Product
Store120,513 20,513
Product 14,13614,136
Product 1 - Sub Cat A19,03010
Product 1 - Sub Cat B20010
Product 1 - Sub Cat C-1,09210
Product 1 - Sub Cat D-13,50010
Product 1 - Sub Cat E-50210
Product 1 - Sub Cat F010
Product 2248,1982252,334
Product 303252,334
Product 4524,8544777,188
Product 5152,3265929,514
Product 620,6756950,189
Product 72257950,414
Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    Hi lyrk8888 ,

     

    Try formula like below:

    Level =
VAR search_ =
    SEARCH ( "*Sub", 'Table'[Store],, 0 )
RETURN
    IF (
        'Table'[Store] = "Store1",
        "level1",
        IF ( search_ = 1, "level3", "level2" )
    )
    M_ =
IF (
    MAX ( 'Table'[Level] ) = "level1",
    MAX ( 'Table'[Amt] ),
    IF (
        MAX ( 'Table'[Level] ) = "level3",
        0,
        CALCULATE ( SUM ( 'Table'[Amt] ), ALLEXCEPT ( 'Table', 'Table'[Level] ) )
    )
)

     

     

    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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

1 Reply

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi lyrk8888 ,

     

    Try formula like below:

    Level =
VAR search_ =
    SEARCH ( "*Sub", 'Table'[Store],, 0 )
RETURN
    IF (
        'Table'[Store] = "Store1",
        "level1",
        IF ( search_ = 1, "level3", "level2" )
    )
    M_ =
IF (
    MAX ( 'Table'[Level] ) = "level1",
    MAX ( 'Table'[Amt] ),
    IF (
        MAX ( 'Table'[Level] ) = "level3",
        0,
        CALCULATE ( SUM ( 'Table'[Amt] ), ALLEXCEPT ( 'Table', 'Table'[Level] ) )
    )
)

     

     

    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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