Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
I tried this way, seems like this is now working . Any suggesions please.
Thank You.
Solved! Go to Solution.
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 )
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 @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 )
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.
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:
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 %.
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |