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
GeekAlfPro
Resolver II
Resolver II

Average by family

Hi,

 

i've a dataset with an id and a family.

i want to calculate the kms average by id so far so good

My issue is when i want to display in front of each id, the family average like below.

 

GeekAlfPro_0-1679926876283.png

 

i don't find the dax code to stay at the family level

1 ACCEPTED SOLUTION

Thanks @Arul 

I finally managed to display what i want 

it was the wrong columns i assume...

i now display the column of the calculate table, with the column of the vehicle just be

 

GeekAlfPro_0-1680020277889.png

 

For those who are inserested, if you want to display the value in a card you muste create a measure : 

 

kms quotidien moyen famille = 
LOOKUPVALUE('Données par Famille'[Kms / jour], 'Données par Famille'[Famille], SELECTEDVALUE('Paramètres Véhicules'[Famille]))

 

 

View solution in original post

10 REPLIES 10
GeekAlfPro
Resolver II
Resolver II

Hello @Arul 

they are indeed in different tables, they are related by the id. 

Here is the data model

 

GeekAlfPro_0-1679998254424.png

in 1 you can see the links between the id and the family.

in 2 i tried to sumarize the date by family in a separated table. i also tried a lookupvalue, but without success.

GeekAlfPro_1-1679998444508.png

 

@GeekAlfPro ,

What are all the columns that you are using in your visual?

Thanks,

Arul





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


i use

'Paramètres Véhicules'[Famille], 'Paramètres Véhicules'[N° Parc] and the measure

 

kms quotidien moyen famille = 
CALCULATE(
	AVERAGE('daily_aggregate_final'[max_distance])
	,ALLEXCEPT('Paramètres Véhicules', 'Données par Famille'[Famille])
)

@GeekAlfPro ,

Am asking the columns and measures used in the visual.

Thanks,

Arul





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Thanks @Arul 

I finally managed to display what i want 

it was the wrong columns i assume...

i now display the column of the calculate table, with the column of the vehicle just be

 

GeekAlfPro_0-1680020277889.png

 

For those who are inserested, if you want to display the value in a card you muste create a measure : 

 

kms quotidien moyen famille = 
LOOKUPVALUE('Données par Famille'[Kms / jour], 'Données par Famille'[Famille], SELECTEDVALUE('Paramètres Véhicules'[Famille]))

 

 

i'm not sure that i well understood the question, but here is a screenshot of my visual : 

 

GeekAlfPro_0-1680005588285.png

 

BrianConnelly
Resolver III
Resolver III

Hello, see the formulas below and the outcomes in the image:

BrianConnelly_0-1679928582976.png

KMS Avg:=AVERAGE([Kms Average])

KMS Family Avg.:=CALCULATE([KMS Avg],FILTER(ALLSELECTED('Table1'[Famille]),'Table1'[Famille]=MAX(Table1[Famille])))

KMS Family Avg v2.:=CALCULATE([KMS Avg],ALLEXCEPT('Table1','Table1'[Famille]))

, but the formula you want is the v2:

KMS Family Avg v2.:=CALCULATE([KMS Avg],ALLEXCEPT('Table1','Table1'[Famille]))

 

Hello @BrianConnelly ,

Thanks for your answer, unfortunately, when i display the measure in a table, the average is recalculed by id

 

GeekAlfPro_0-1679936731068.png

 

@GeekAlfPro ,

Are the ID column and Famille column comes from different table? If yes, do they have reltionship and what is it?

Thanks,

Arul





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


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.