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.

Icey's avatar
Icey
Icon for Community Support rankCommunity 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
    Icon for Community Support rankCommunity 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!