Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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_'s avatar
Arnault_
Resolver III
7 years ago

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