Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure to calculate average in line stack bar chart

 

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.

 
 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank You very much. This work as expected.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.