Forum Discussion

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

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

Not applicable

6 years ago

Hi,

My data structure is this:

TimePeriod	Brand_Cat	Sales
This Month	Cat	100
This Month	Pepsi	50
This Month	Coca-Cola	50
Last Month	Cat	200
Last Month	Pepsi	150
Last Month	Coca-Cola	50
Past 3 Months	Cat	3000
Past 3 Months	Pepsi	1000
Past 3 Months	Coca-Cola	2000

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

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
Not applicable
6 years ago
Absolutely, this is what I needed! Thanks a lot!