Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using What If Parameter to Calculate a Aging

We have overdue invoices and I'd like to set up a dashboard for users to see the total amount outstanding by X number of days past the due date. I have all the fields and it works on a row by row basis but where I'm getting stuck is the aggregation. 

 

I have to use the age as a measure to get the formula to work but when I remove the transaction numbers, it sums the age and literally everything in the dataset meets the criteria. This also happens in the totals. 

 

What's the correct syntax/structure to apply row level filtering using a what-if parameterized metric so that only transaction numbers meeting the criteria are calculated in the total? We'd still like to see the total amout outstanding in the same chart.

 

Thanks.

 

 

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hello @MikeBode ,

    You must perform an aggregation calculation using SUMX something similar to:

    SUMX(Filter(Table,[Agemeasure]>=[Parameter Over] ); SUM(Table[Amount]))

    This may require adjustments based on the context of your calculation.

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hello @MikeBode ,

    You must perform an aggregation calculation using SUMX something similar to:

    SUMX(Filter(Table,[Agemeasure]>=[Parameter Over] ); SUM(Table[Amount]))

    This may require adjustments based on the context of your calculation.

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous 

    Another option is to include an "elapsed days" by calculating the days due using the "Age" and "Duration" functions in Power Query (based on the due date column in your data) to have the elapsed days as a column in the dataset. Since it will be part of a table, the values will be a constant value in the table itself.