Forum Discussion

Sasha's avatar
Sasha
Helper III
6 years ago

Key influencers analyzing measure

Hi, I'm trying to implement the new feature of analyzing measure in the key influencers visualization. Suppose I have a table of sales with columns Region, Month, Units Sold, Total Revenue. I woul...
Need Help
Sasha's avatar
Sasha
Helper III
6 years ago

Hi v-xuding-msft 

Here's a sample of my data:

 

I add a measure: 

Revenue/Unit = DIVIDE(SUM(Sales[Total Revenue]),SUM(Sales[Units Sold]))
 
I then want to analyze this measure all columns:
Without filtering total revenue is $594100 and total units sold 144661
So Revenue/Unit = 594100/144661 = $4.11
However, the visualization compares the segments to other average.
What it basically does - it calculates the measure on a row level and then averages all results resulting in $4.39.
 
I would like it to compare to $4.11 - is it possible (otherwise - why am I given the option to analyze by measure?)
 
 
v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi Sasha ,

How about adding CALCULATE per sum values? Did you try this? Did it work?

Revenue/Unit = DIVIDE(CALCULATE(SUM(Sales[Total Revenue])),CALCULATE(SUM(Sales[Units Sold])))

Due to the sample data is a part of your data, I'm not sure the result I tested is what you want. You could try this firstly. If there is anything wrong, please point it.

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Sasha's avatar
    Sasha
    Helper III
    6 years ago

    v-xuding-msft  Hi,

    it didn't change anything :(

    • v-xuding-msft's avatar
      v-xuding-msft
      Community Support
      6 years ago

      Hi Sasha ,
      Please try the following formula.

      Revenue/Unit = 
var a = CALCULATE(SUM(Sales[Total Revenue]),ALL(Sales))
var b = CALCULATE(SUM(Sales[Units Sold]),ALL(Sales))
return
DIVIDE(a,b)

      Best Regards,

      Xue Ding

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Sasha's avatar
        Sasha
        Helper III
        6 years ago

        v-xuding-msft 

        Thank you!

        However, this will result in a constant number of Revenue/Unit - meaning for every filter it will return the grand average - so there won't be any difference between a particulr group average and the total average.

        By the way - in the file you uploaded - if I remove Months (or other columns) from "explain by" and then return it back the results change - it seems like the visualization is not stable.