Measure: Aggregate By, Filter By, Group By, Simple Math

[Anonymous]

I am attempting to make a measure that calculates SUM(Num_Compliant) / SUM(Num_Asked) for a District, filtered to the current Fiscal Year (i.e. Date on or after 07/01/2018, or July 1, 2018).

With the following sample data,

I expect the following results:

District	Numerator	Denominator	Value
A	4	14	0.29
B	10	14	0.71
C	6	14	0.4 3

I am aware that the denominators are always 14 in this sample data, but that is not the case in the real data, as, in practice, sections are sometimes missing.

I have been trying to solve this for about a day, and am brand-new to Power BI, so I apologize if this is a very simple question.

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could try this way as below:

Is there a Fiscal Calendar table in your report, if not add a Fiscal Calendar table then create a relationship with basic data table.

Then create measure as below:

Total NumAsked = SUM('Table'[Num_Asked])

Total NumCompliant = SUM('Table'[Num_Compliant])

Value = DIVIDE([Total NumCompliant],[Total NumAsked])

Result:

here is pbix file, please try it.

Best Regards,

Lin

[Arnault_]

Hi @Anonymous ,

There are different possible answers. Here is a link with a pbix file with the desired output.

I chose to create a table to identify the fiscal year period. It is better to keep it dynamic and allow users to change it.

Please check the file, the different measures created and give me your feedback.

Cheers

Pbix