cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Per 1000 per month

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.

12 REPLIES 12
Microsoft Employee

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

Helper I

Hi Alex,

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

Thanks Linda

Microsoft Employee

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

Helper I

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

Microsoft Employee

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

Helper I

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

Microsoft Employee

Hi LindaHa,

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

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

Helper I

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

Microsoft Employee

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

Super User

Can you provide some sample data?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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

Helper I

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

Announcements