Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure with Static Denominator

I have a data set with volume/value and column with brands and categories in the same column. I need to calculate volume/value share which sales for brand/sales for category. However, when I am putti...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

    Is this what you want?

    ParentSales = IF(SEARCH("Cat",'Table'[Brand_Cat],,0)<>0,'Table'[Sales])
    ParentSales 1 = 
VAR LastNonBlankSales =
    CALCULATE (
        LASTNONBLANK ( 'Table'[ParentSales], 1 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index] <= EARLIER ( 'Table'[Index] )
                && NOT ( ISBLANK ( 'Table'[ParentSales] ) )
        )
    )
RETURN
    CALCULATE (
        SUM( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Sales] = LastNonBlankSales )
    )
    Percent = DIVIDE(MAX('Table'[Sales]),MAX('Table'[ParentSales 1]))

     

    Best Regards,

    Icey

     

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi This is really helpful. But can you really help if I have data across time periods too suppose the same data is for 4 different time periods.

 

or for example in excel: I can look for time period and category combination and populate it for brands.

Icey's avatar
Icey
Community Support
6 years ago

Hi Anonymous ,

Please give me a table with a complete data structure, I will conduct a specific test. You can use DropBox, GoogleDrive, OneDrive, etc. Attach the link.

 

Best Regards,

Icey

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

     

    My data structure is this:

    TimePeriodBrand_CatSales
    This MonthCat100
    This MonthPepsi50
    This MonthCoca-Cola50
    Last MonthCat200
    Last MonthPepsi150
    Last MonthCoca-Cola50
    Past 3 MonthsCat3000
    Past 3 MonthsPepsi1000
    Past 3 MonthsCoca-Cola2000

     

    Sorry I can't access any cloud service from my network.

    • Icey's avatar
      Icey
      Community Support
      6 years ago

      Hi Anonymous ,

      Is this what you want?

      ParentSales = IF(SEARCH("Cat",'Table'[Brand_Cat],,0)<>0,'Table'[Sales])
      ParentSales 1 = 
VAR LastNonBlankSales =
    CALCULATE (
        LASTNONBLANK ( 'Table'[ParentSales], 1 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index] <= EARLIER ( 'Table'[Index] )
                && NOT ( ISBLANK ( 'Table'[ParentSales] ) )
        )
    )
RETURN
    CALCULATE (
        SUM( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Sales] = LastNonBlankSales )
    )
      Percent = DIVIDE(MAX('Table'[Sales]),MAX('Table'[ParentSales 1]))

       

      Best Regards,

      Icey

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Absolutely, this is what I needed! Thanks a lot!