Forum Discussion

ToshSatellite's avatar
ToshSatellite
New Member
9 years ago

count and weighted average

Hi all,

 

I have 2 tables - one called (1) 'Individual Claims' and the other one called (2) 'Merged MPs-TS-OI'.

In table (1) I have 1 column (among others) called 'Amount Claimed'.
In table (2) I have 3 columns (among others) called 'MP Name', 'Party' (which UK political party the MP belongs to) and 'Period' (2010-2011, 2011-2012, etc.). The 2 tables are linked via a third table with unique values.

 

I've created a calculation to figure out the total number of MPs per period:

 

 

[Total No of MPs by Period] = CALCULATE(DISTINCTCOUNT('Merged MPs-TS-OI'[MP Name]), ALL('Merged MPs-TS-OI'[Period]))

 

After that, I tried to create a calculated measure to figure out the percentage of MPs per party per period in relation to the total number of MPs per period with the following formula:

 

[Ratio of MPs by Period] = DISTINCTCOUNT('Merged MPs-TS-OI'[MP Name]) / 'Merged MPs-TS-OI'[Total No of MPs by Period]

 

Basically I want a number in percentage that tells me how much a specific political party represents for each period, for example the Conservatives represented 42% of the total number of MPs for the period 2013-2014, etc. Once I get that number, I will then be able to multiply it by the amount claimed per MP and get a weighted average.

 

However, the second calculation is not working and is giving me very weird results, e.g. 216.67, 650.00, infinity, etc. And this is even before I attempted to calculate the weighted average!

 

Can anyone help please?

 

Thanking you in advance.

 

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Because you are using the ALL, this ignores all context filters, which would include your Period, so you would end up with weird results most likely.

     

    Perhaps just use:

     

    MyCount = DISTINCTCOUNT('Merged MPs-TS-OI'[MP Name]) 

    And then use a Quick Measure for % of Total?

    • ToshSatellite's avatar
      ToshSatellite
      New Member
      9 years ago

      Thank you smoupre for your quick reply but I'm not sure I understand. If I don't use the ALL keyword, this will count all the MPs in the table, will it not?

       

      Also what do you mean by Quick Measure for % of Total?

       

      Would you have specific examples you could share please?

       

       

       

      (You'll have to excuse my ignorance but I just literally started using Power BI last week!) 

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    9 years ago

    Any chance you could provide a sample pbix, and your desired result? this makes creating the required measure much easier.

     

    Thanks,