Forum Discussion

Stijn123's avatar
Stijn123
Regular Visitor
2 years ago

Retrieve automatically calculated Total value for future calculations

Dear community,   I have a table that is subjected to many filters and I want the value that is calculated by the automatically calculated Total row as a separate measure. I added a screenshot of a...
Stijn123's avatar
Stijn123
Regular Visitor
2 years ago

Thank you very much for your reply! Unfortunately this does not work since the table is filtered with multiple measures and your solution provides only the total sum of all the values in that column. Another answer would be very much appriciated!

  • 123abc's avatar
    123abc
    Community Champion
    2 years ago

    I understand that you need to calculate the Total value considering the filters applied by multiple measures, not just a simple sum of the column. To achieve this, you can use the "ALL" and "FILTER" DAX functions to remove the filters applied by other measures and then calculate the Total value.

    Here's a general approach to do this in Power BI:

    Assuming you have multiple measures (Measure1, Measure2, etc.) that filter your table, and you want to calculate the Total value based on these filtered measures:

    1. Create a new DAX measure for the Total value. You'll use the "ALL" and "FILTER" functions to remove the filters and then calculate the total. For example:

    Total Value = CALCULATE(
    [YourCalculationMeasure], -- Replace with your calculation logic
    ALL(YourTable), -- Remove all filters from the table
    FILTER(YourTable, NOT(ISBLANK([YourMeasure1]))) &&
    FILTER(YourTable, NOT(ISBLANK([YourMeasure2]))) &&
    ... -- Repeat for all relevant measures
    )

     

    Replace [YourCalculationMeasure] with your calculation logic, and [YourTable], [YourMeasure1], [YourMeasure2], etc., with the appropriate table and measure names that are relevant to your scenario.

    1. Place this "Total Value" measure in a card visual in your report.

    2. As you apply filters using the multiple measures, the "Total Value" measure will consider these filters and calculate the Total accordingly.

    This approach uses the CALCULATE function with the ALL and FILTER functions to create a measure that considers the filters applied by multiple measures and then calculates the Total value. The key is to use the FILTER function to check if the measures are not blank before applying the ALL function to remove the filters from the entire table.

    This way, you can get the Total value that takes into account the filters from multiple measures and use it for future calculations. Adjust the DAX expressions as needed to fit your specific scenario.

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.