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
Chavas
Helper II
Helper II

Calculate average per month based on status

Hello all. 
I am struggling with a metric which should be easy, but I do not know why it is not showing the information.
I have a table with the connection status of multiple entities. Each row means a connection status per day per entity, as a sample:

Entity CodeDATEConnection Status
111-08-19A
211-08-19A
311-08-19B
111-08-20A
211-08-20B
112-08-20B
212-08-20C
101-09-20A
201-09-20B
301-09-20C



So I have 4 graphs based on the status showing the total amount and compared with other years. As an example, for Status A:

Chavas_0-1597157804529.png


Nevertheless now I would like to show it in % of the total that month. So I have created the next metric:
 

Status Average = CALCULATE(COUNTA(ConnectionStatus[EntityCode]),ConnectionStatus[Connection_Status]="STATUS A")
/ COUNTA(ConnectionStatus[EntityCode])

Nevertheless when applied to the visual is not showing the proper value but this:
Visual with Metric appliedVisual with Metric applied

In top of that, I would love to make one single metric, not one for each Connection_Status, is that possible? I am not being able to use the SELECTEDVALUE function, but I bet because I cannot apply it into the visual itself.

I have tried to play with the Average(s) functions but are not working properly for me.


Thanks in advance
1 ACCEPTED SOLUTION
Chavas
Helper II
Helper II

I had the visual filtered. Once removed the filter it starts to work.

 

I had created one metric for each status.

View solution in original post

4 REPLIES 4
Chavas
Helper II
Helper II

I had the visual filtered. Once removed the filter it starts to work.

 

I had created one metric for each status.

amitchandak
Super User
Super User

@Chavas , Not very clear. Can you share sample data and sample output in a table format?

 

refer if this can help https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

Sure, first thing first. There was a Typo, now it is showing all as 1

 
 
 

Visual with Metric appliedVisual with Metric applied



Sample of table:

Entity CodeDATEConnection Status
111-08-19A
211-08-19A
311-08-19B
111-08-20A
211-08-20B
112-08-20B
212-08-20C
101-09-20A
201-09-20B
301-09-20C

My bad...

God, visual filter was applying to the measure that is why it was not working propery.
So, is anyway that I can avoid hardcoding the Filter on the metric and set it on the visual?

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.

Top Solution Authors