Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Measure Referencing a Single Result

Hi Friends,

Been going around in circles trying to figgure this out. I am wanting to target a single result of a measure and use it for more calculations and I specifically want to do this using a matrix visualisation. Example image is below
Dummy Report Image

What I am trying to do is create a measure (Called Mix) that references a single result of an existing measure [Total sales] and run a calculation. The measure will roughly translate to something like Store Total sales / Category Sales and expressed as a percentage.

Using Store 8 as an example, I would like it to look something like this 
Desired Result

Because this sits in a Matrix Visualation is hard to isolate results of a single category
Running out of ideas.

Here is an image of my table of data which is nothing fancy
Data Table
The heirachy runs  Region > Store > Category.

Any help is appreciated

Need Help

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Anonymous ,

     

    I'm afraid you should convert [Store Total Sales] and [Category Total Sales] into calculation column and then do the divide operation.

     

    Regards,

    Jimmy Tao

  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago

    To get the result you show for the Store 8 picture (next time just insert the pictures using the "photo" option, jumping back and forth to google drive images makes it hard to read your post) you could do something like the following:

     

    Mix = DIVIDE( SUM( table[Retail] ) , CALCULATE( SUM( table[Retail] , ALL( table[Category] ) ) )

     

    Although that only works at the category level, if you want something that works differently as you work your way down the Region / Store / Category hierarchy you would need to use a pattern like the following:

    Mix =
    IF( HASONEVALUE( table[Category] ) 
      , DIVIDE( SUM( table[Retail] ) , CALCULATE( SUM( table[Retail] , ALL( table[Category] ) ) )
      ,  IF( HASONEVALUE( table[Store] ) 
        , DIVIDE( SUM( table[Retail] ) , CALCULATE( SUM( table[Retail] , ALL( table[Store] ) ) )
        , DIVIDE( SUM( table[Retail] ) , CALCULATE( SUM( table[Retail] , ALL( table[Region] ) ) )
    )