Per 1000 per month

[LindaHa]

Hello,
 
I am trying something simular. I have a list with classifications but I need those per month per 1000 sectors . The per month separation works fine but I do not know how to get in the per 1000. In Excel I was able to do it with = (number of classification)*1000/(real number of sectors) but when I use this in BI the result is empty.
 
 In fact I want to show my amount of classification per month per 1000 sectors in a graph.
 
Thanks for any assistance.
 

[AlexChen]

Hi,

I assume your first table called “accident” and the second table called “flights”.

I changed the records in these 2 table in order to give an example.

First,  You need to build a relationship between accident[month] and flights[Month].

Now You can add a calculated column to calculate the value:

Column = DIVIDE(CALCULATE(COUNTA(accident[classification]), RELATEDTABLE(accident)) * 1000, [TTL])

This is the result:

Best Regards

Alex

[LindaHa]

Hi Alex,

thanks for this! Could you please explain to me why I have to add an ID?

Thanks Linda

[AlexChen]

Hi LindaHa,

I add an ID just to give a example to show the records clearly. You don't have to add it.

Best Regards

Alex

[LindaHa]

Hi Alex,

I think I got a little closer to my goal with your assistance. Thank you very much. Unfortunately I did not get the result per month per classification. It is all the same for classification 😕

Do you have a tip for me how I can split it for the classifications so that they do not have all the same value?

Best regards Linda

[AlexChen]

Hi,

You can create a table to get classification per month.

monthlyClassInfo = SUMMARIZE(accident, accident[month], accident[classification], "count of classification", COUNTA(accident[classification]))

Create relationship for monthlyClassInfo[month] and flights[month].

Now you can add a column in monthlyClassInfo to calculate per classification per month.

Column = DIVIDE([count of classification] * 1000, RELATED(flights[TTL]))

Best Regards

Alex

[LindaHa]

Hi Alex,

thanks for your tip. When I try: monthlyClassInfo = SUMMARIZE(accident, accident[month], accident[classification], "count of classification", COUNTA(accident[classification]))

It says: "Function SUMMARIZE expects a column name as argument number 5" when I try to add COUNTA

Best regards Linda

[AlexChen]

Hi LindaHa,

I can't reproduce your error. It works fine in my local. The "CountA" should be there.

I advise you take a look at this article https://msdn.microsoft.com/en-us/library/gg492171.aspx

to see the syntax of SUMMARIZ and check you code again.

Please also make sure you are using the latest Powerbi desktop.

Best Regards

Alex

[LindaHa]

Hi AlexChen,

I think my issue is that the Count of classification is not a calculated column. Can you tell me how I can make it. For me it only works as a measure.

Thanks Linda

[AlexChen]

Hi Linda,

You can create 2 measures for "accident" table.

countOfClassification = COUNTAX(accident, accident[classification])

measureRate = SUMX(accident, DIVIDE(accident[countOfClassification] * 1000, RELATED(flights[TTL])))

This is the result:

Best Regards

Alex

[Greg_Deckler]

Can you provide some sample data?

[LindaHa]

This is my graph. I need exactly this but not with absolute numbers but with "amount of classification per month per ttl flights".

current graphRaw dataper sector values

[LindaHa]

so the x-axis should not be the real numbers but the "classification per 1000 flights".