Latest Enterprise DNA Initiatives

Calculating Weighted Average not involving dates

Hi, I have been struggling to calculate some form of Weighted Average for 7 incident groups (“Our Mosaic Group”) based on property(population) size on a different table.

I’m ideally looking to rank the incidents count based on the “weight” of the property size
So instead of counting the no of incidents per group, I’m looking to weigh it based on the population size : “Properties” on a different table.
Table 1 (Fire data) has incidents and Table 2 (Groups Data )has the property size.
The relationship:

Linked by Mosaic type field
The formula I used is this:

weighted Average =

DIVIDE(

SUMX('Fires Data cleaned 2', [Count of Incidents] * RELATED('Groups Data'[Properties])),

SUMX('Fires Data cleaned 2',[Count of Incidents] )

)

But I wanted to check that it is the right way of looking at it.
Attached is my pbix fileSegmentation annon.pbix (204.9 KB)

Hi @Ikay, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

Please has anyone had a look yet? am I missing any info.
THank you so much

Hi @Ikay - Check if you can use below measure.

Group 1 weigthed =
VAR Group1Count =
    CALCULATE (
        COUNT ( 'Fires Data cleaned 2'[Fire count] ),
        'Fires Data cleaned 2'[Our Mosaic Group] = "1"
    )
VAR Group1Populaion =
    CALCULATE ( SUM ( 'Groups Data'[Population] ), 'Groups Data'[Our Group] = "1" )
RETURN
    DIVIDE ( Group1Populaion, Group1Count, 0 )

“Related” function won’t work in your case as there is a mismatch b/w names in “Mosaic Type” in Fires Data and Group Table and the Relationship is setup using this. For example

Groups Data - L50 Renting Rooms,O63 Single Essentials
Fires Data - L50 Renting a Room, O63 Streetwise Singles

Thanks
Ankit J

Thank you so much @ankit for pointing that out. I’ve amended the Groups Data to match. I’m not sure the using the dax above solves it, because its making Group for more than the rest, when it has the least No. of Incidents and least Population size (using this on live data). I have attached the amended pbix file

Also, I’m not sure if its possible but Ideally, I’m looking for just one Dax code that can be filtered by the groups and sorted according to the highest, instead of having multiple dax code for each group.

@Melissa, please chip in, if you can.

THANK YOU SO MUCHSegmentation annon.pbix (206.7 KB)

Hi @Ikay, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @Ikay,

Please provide a mock up of de desired results based on your updated sample data, an XLSX will do.
Thanks.

mockup.xlsx (8.8 KB)

Thanks @Melissa, please find attached.

Thank you

I seem to be looking at different figures…
image

please explain

I’m really sorry for late response What I sent was using real data. But you are right. there are 2 different figures, so I’m essentially looking for a way to “rank” or “weight” the Incidents counts on the “Fire Data Cleaned” table (what I sent), using/based on the sum of population on the “Groups data” table.

Hi @Ikay,

Think I’m still a bit lost here but let’s see what you make of this.
If this is not moving in the right direction then post a mock up based on this sample file. Thanks

Fires count = 
COUNT( 'Fires Data cleaned 2'[Our Mosaic Group] )

Total Fires count = 
CALCULATE( COUNT( 'Fires Data cleaned 2'[Our Mosaic Group] ),
    ALLSELECTED( 'Date Table' ),
    ALLEXCEPT( 'Fires Data cleaned 2', 'Fires Data cleaned 2'[Date of Call] )
)

Population = 
SUM( 'Groups Data'[Population] )

Total Population = 
CALCULATE( SUM( 'Groups Data'[Population] ),
    REMOVEFILTERS( 'Groups Data' )
)

And for the avg with population weighting

Avg Weighting = 
VAR Groups = VALUES( 'Groups Data'[Our Group] )
VAR vTable =
    ADDCOLUMNS(
        ADDCOLUMNS( Groups,
            "@Pop", DIVIDE( [Population], [Total Population] ),
            "@Fire", DIVIDE( [Fires count], [Total Fires count] )
        ),  
        "@Result", IF( NOT( ISBLANK( [@Pop] )) && NOT( ISBLANK( [@Fire] )), 
            DIVIDE( [@Fire], [@Pop] ) * [Fires count]
        )
    )
RETURN
    AVERAGEX( vTable, [@Result] )

image

Here’s your sample file. Segmentation annon.pbix (190.9 KB)

I hope this is helpful.

Thanks @Melissa, this seems to work. I can’t say thank you enough.

Please if you can , it will be good to understand what the avg population weighting is doing.

Thank you again

Hi @Ikay,

I trust this mock up will help you.
avg population weighting.xlsx (10.4 KB)

1 Like

Awesome!. Thank you again.