How to Use Total Average instead of 3 or 4 rows divided by #rows ?

[Chanleakna123]

Hi All , I wanna visual using the total Average 1768.60 only , and then i use this AVR Number to calculate  Segmentationin each cusstomer code . I tryna play around , but not as expected. look like it's still show into 3 rows and then the segmentation will turn wrong. If we use the total Average of Each Customer Code it will turn right. 

***Avr = AVERAGE('Outlets Master List'[Value])

***Segmentation = IF([Avr]>=RELATED('Master List Condition'[Bronze Min]) && [Avr]<RELATED('Master List Condition'[BronzeMax]),"Bronze",

IF([Avr]>=RELATED('Master List Condition'[Silver Min ]) && [Avr]<RELATED('Master List Condition'[Silver]),"Silver",

IF([Avr]>=RELATED('Master List Condition'[Gold Min]) && [Avr]<RELATED('Master List Condition'[Gold Max]),"Gold")))

 My Expectation result : 

Customer Code	Avr	Segmentation
200005986	1768.6	Gold

[Stachu]

the [Avr] is calulated in the specific filter context, so right now it calculates for each customer

I'd try the following

***Avr Total Customer = 
CALCULATE(
    AVERAGE('Outlets Master List'[Value]), 
    ALL('Outlets Master List'[Customer Code])
)

[Chanleakna123]

hi @Stachu 

i did as per what you mentioned , but it doesn't give me desired result. I still can see the Customer Code show with Seperated value rather than the total Average value. Therefore , when i connect with Segmentation i did above calculation , it turn out into different valvues rather than total Average. And i dun wanna see the right result when filtering , because i will visual this with table , therefore it can turn out the same value if we don't filtering.  

PS : when i take out Segmentation in table , the data show total Average. 

can you please help me ? 

[Stachu]

can you describe your data model? what tables are you using, and how are they joined? please attach sample rows for each table

[Chanleakna123]

hi @Stachu  above is the value i will combine together to find out Total Average for each customers. and both pic are in relationship together 

This second pic : is the Condition i will apply for calculation to find out which customer is bronze , silver and gold , i already share the calculation segmentation above. 

but when i apply the segmentation calculation as shared above, the visualization show each value of each months. while i don't need this , i need only the Total Average , to see the Segmentation. 

[v-frfei-msft]

Hi @Chanleakna123 ,

Here I created two measures to work on it.

ave = AVERAGEX(ALLSELECTED('Outlets Master List'),'Outlets Master List'[value])

Measure 2 = var Bronzemin = CALCULATE(MAX('Master List Condition'[Bronze Min]),ALL('Master List Condition'))
var bronzemax = CALCULATE(MAX('Master List Condition'[BronzeMax]),ALL('Master List Condition'))
var SilverMin = CALCULATE(MAX('Master List Condition'[Slivers Min]),ALL('Master List Condition'))
var SilverMax = CALCULATE(MAX('Master List Condition'[Sliver]),ALL('Master List Condition'))
var Goldmin = CALCULATE(MAX('Master List Condition'[Gold Min]),ALL('Master List Condition'))
var Goldmax = CALCULATE(MAX('Master List Condition'[Gold Max]),ALL('Master List Condition'))
return
if([ave]>=Bronzemin && [ave]<=bronzemax, "Bronze",IF([ave]>=SilverMin&& [ave]<=SilverMax,"Silver",IF([ave]>=Goldmin && [ave]<= Goldmax,"Gold")))

Please check the pbix as attached.

Regards,

Frank

[Chanleakna123]

Hi @v-frfei-msft  really appreciated your quick support . but still not really friendly with this report. 

I mean Segmentation can be seen right unless we filter by code . if we don't filter by code , it will turn all data to wrong. and i understand this . 

but Is it possible that we use Table Visualization to see all Total Average by each code + Segmentation ? 

Since this will be easier for reader to read the report and not confused. 

[v-frfei-msft]

Hi @Chanleakna123 ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.

Regards,

Frank

[Chanleakna123]

Hi @v-frfei-msft  , 

https://www.dropbox.com/s/megzn7ocvmmlvmz/Segmentation%20Data.pbix?dl=0

[Stachu · Accepted solution]

try using this for average:

ave =
CALCULATE (
    AVERAGE ( 'Outlets Master List'[value] ),
    ALL ( 'Outlets Master List'[Customer name], 'Outlets Master List'[code] )
)

basically you need to add all the columns you want to ignore to the in the ALL

if you want to get the global total average regardless of any filter context coming from different dimensions then just use ALL(Table), like here

ave =
CALCULATE (
    AVERAGE ( 'Outlets Master List'[value] ),
    ALL ( 'Outlets Master List')
)

[Chanleakna123]

@Stachu i think maybe i use  manually to calculate into total Avr and upload into BI , i used your calculation but i still see the duplicated of customer name appear in each value. while the segmentation will be based on the Total Average of each customer rather than drill down each value of each customer. 

Anw , i confuse to Click Accept solution 😞 

ave =
CALCULATE (
    AVERAGE ( 'Outlets Master List'[value] ),
    ALL ( 'Outlets Master List'[Customer name], 'Outlets Master List'[code] )
)

[Stachu]

have you used the same columns in the visual and the measure?

[Chanleakna123]

Hi stachu, can u please help
Me
To get as desired result as provided above ? I am distracted here.