Measure to calculate average in line stack bar chart

[Anonymous]

Hi,
I have trouble with calculate average line for specific stack in line and stack bar chart, below is the sample table I have,

 

And I will drop the sample visyalization design

 

I need measure to calculate line for count average email on each mobile source.

measure= CALCULATE( AVERAGE('table'[email]), FILTER('table', 'table'[Source] = "mobile"))

I tried this way, seems like this is now working . Any suggesions please.

Thank You.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Sorry for my misunderstanding...

Please try this:

avg for each cate =
VAR _each =
    CALCULATE (
        COUNT ( 'Table'[Email] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Categoty] = MAX ( 'Table'[Categoty] )
                && 'Table'[Source] = "mobile"
        )
    )
VAR _total =
    CALCULATE (
        COUNT ( 'Table'[Email] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Categoty] = MAX ( 'Table'[Categoty] ) )
    )
RETURN
    DIVIDE ( IF ( _each = BLANK (), 0, _each ), _total )

Here is the pbix file.

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

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @Anonymous , 
I got issue in average line, its values didnt change with the date slicer. It displays same percentage on full data range even change the slicer. 
Do you have any idea on that ?

thank you

[Anonymous]

Thank You very much. This work as expected.

[Anonymous]

Hi @Anonymous ,

Since the data type of your Email column is Text not Number , you could not use AVERAGE() directly.

According to my understand, you want to calculate count mobile for each Categoty

1. divide count  total number for each Category

avg for each cate =
VAR _each =
    CALCULATE (
        COUNT ( 'Table'[Email] ),
        FILTER (
            'Table',
            'Table'[Categoty] = MAX ( 'Table'[Categoty] )
                && 'Table'[Source] = MAX ( 'Table'[Source] )
        )
    )
VAR _total =
    CALCULATE (
        COUNT ( 'Table'[Email] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Categoty] = MAX ( 'Table'[Categoty] ) )
    )
RETURN
    DIVIDE ( _each, _total )

2.divide count all mobile number.

avg for all mobile =
VAR _countInEachCate =
    CALCULATE (
        COUNT ( 'Table'[Email] ),
        FILTER (
            'Table',
            'Table'[Categoty] = MAX ( 'Table'[Categoty] )
                && 'Table'[Source] = "mobile"
        )
    )
VAR _all =
    CALCULATE (
        COUNT ( 'Table'[Email] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Source] = "mobile" )
    )
RETURN
    DIVIDE ( _countInEachCate, _all )

The final output looks like this:

Here is the pbix file.

Best Regards,
Eyelyn Qin

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

[Anonymous]

Hi,
I think you didnt get my question correctly. Its my problem with explaining.

As you can see tha graph is fine. The issu is with the line. I wanteded to display average count through line. 
Ex: when we considerFirst bar I want to display value 214/274 , second bar 175/188 etc.
     It can be whole number or %.

Can you give me a solution, I earlier tried with COUNTROWS , but no luck. 
Can you assume X and Y axis are same as earlier. 
Can we go with COUNTROWS, because If their is 3 duplicates with same email I want to count as 3. Thats the reason.

Thank You.