Measure with Static Denominator

[Anonymous]

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 putting it against a matrix in front of brand list. I am getting infinity. Please can you help me if there is a workaround.

[Icey]

Hi @Anonymous ,

Please give me some sample data.

Best Regards,

Icey

[Anonymous]

Sample: Two Columns Brand/Cat and Sales

Brand_Cat            Sales

Drinks (cat)          100

Coca-Cola            50

Pepsi                    50

I want to calculate brand share for coca-cola as 50/100. But the problem is when I make a matrix of brand_cat vs brand share. I am getting infinity error. Because as I understand in the row for coca-cola, the denominator becomes 0.

Can you help with a workaround please.

[Icey]

Hi @Anonymous ,

Try this:

1. Add Index column in Power Query Editor.

2. Create columns.

ParentBrand = IF(SEARCH("cat",'Table'[Brand_Cat],,0)<>0,'Table'[Brand_Cat])

ParentBrand 1 =
VAR LastNonBlankBrand =
    CALCULATE (
        LASTNONBLANK ( 'Table'[ParentBrand], 1 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index] <= EARLIER ( 'Table'[Index] )
                && NOT ( ISBLANK ( 'Table'[ParentBrand] ) )
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( 'Table'[ParentBrand] ) ),
        BLANK (),
        CALCULATE (
            MAX ( 'Table'[Brand_Cat] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Brand_Cat] = LastNonBlankBrand )
        )
    )

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 (
        MAX ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Sales] = LastNonBlankSales )
    )

 3. Create Percent Measure.

Percent Measure = DIVIDE(SUM('Table'[Sales]),MAX('Table'[ParentSales 1]))

PBIX file attached.

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]

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]

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]

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 · Accepted solution]

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]

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