count and weighted average

[ToshSatellite]

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.

[richbenmintz]

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

Thanks,

[Greg_Deckler]

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]

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!) 

[v-ljerr-msft]

Hi @ToshSatellite,

Could you post your table structures with some sample/dummy data and your expected result against that data? So that we can better assist on this issue. 

Regards

[Greg_Deckler]

It will count only distinct values for MPs and this will be context sensitive. So, for example, if you had a table with some other data column, like Period for instance, you would get a count of all distinct values of MP for each period. Quick Measures can be accessed in the fields area of your visual, hover your mouse over the column/measure, click the drop down arrow and choose Quick Measure and then % of Total.

And I may certainly not understand exactly what you are going for.

[dilumd]

Check these out.