Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dilantha
Post Patron
Post Patron

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,

 

pb1.PNG

And I will drop the sample visyalization design

 

pb2.PNG

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.

 
 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @Dilantha ,

 

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 )

12.11.3.2.PNGHere 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

 

View solution in original post

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

Hi @Dilantha ,

 

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 )

12.11.3.2.PNGHere 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

 

Hi @v-eqin-msft , 
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

Thank You very much. This work as expected.

v-eqin-msft
Community Support
Community Support

Hi @Dilantha ,

 

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:

12.11.3.1.PNG

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.

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 %.

Dilantha_0-1607668402957.png

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.