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

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.

8 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

    Please give me some sample data.

     

    Best Regards,

    Icey

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Icey
        Community Support
        6 years ago

        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.