Create a measurement with filter options from one column

[alexando]

Hi,

I am net to DAX expressions but i need to create a measurement or filter for my bargraphs .

I have a table with transactions of different types. One column is the transaction value and one is the transaction type.

I want to be able to present sum of transaction value for different combinations of transatction types.

e.g. one series with type "A", one series with ""B" and one series with "A" & "B". Preferrably selectable trough filter options next to the graph.

What is the best way to accomplish this? I would really appreciate any help.

[Poojara_D12 · Accepted solution]

Hi @alexando 

Create a Helper Table: Add a table with group names like "A", "B", and "A & B" using this DAX:

TransactionTypeGroups = DATATABLE(
    "Group", STRING,
    { {"A"}, {"B"}, {"A & B"} }
)

Create a Measure: Define a measure to calculate the sum of transaction values based on the selected group:

FilteredTransactionValue =
VAR SelectedGroup = SELECTEDVALUE(TransactionTypeGroups[Group])
RETURN
    SWITCH(
        SelectedGroup,
        "A", CALCULATE(SUM(Transactions[TransactionValue]), Transactions[TransactionType] = "A"),
        "B", CALCULATE(SUM(Transactions[TransactionValue]), Transactions[TransactionType] = "B"),
        "A & B", CALCULATE(SUM(Transactions[TransactionValue]), Transactions[TransactionType] IN {"A", "B"}),
        BLANK()
    )

Add a Slicer: Use the TransactionTypeGroups[Group] column as a slicer to let users select "A", "B", or "A & B".

Build the Bar Chart: Add FilteredTransactionValue to the bar chart's Values field to show dynamic sums based on the slicer selection.

This approach allows users to easily toggle between transaction type groups on the chart.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[v-jiewu-msft · Accepted solution]

Hi @alexando ,

Based on the information, try using the following DAX formula. Using the FILTER function within the CALCULATE to ensure that the context of the period filter is considered when calculating the sum.

FilteredBudgetValue = 
VAR SelectedGroup = SELECTEDVALUE(BudgetTypeGroups[Group])
RETURN
    SWITCH(
        SelectedGroup,
        "Actual", CALCULATE(SUM(Transactions1[balance]), Transactions1[Type] = "Actual"),
        "Budget", CALCULATE(SUM(Transactions1[balance]), Transactions1[Type] = "Budget"),
        "FC1", CALCULATE(
            SUM(Transactions1[balance]), 
            FILTER(
                Transactions1, 
                (Transactions1[Type] = "Actual" && Transactions1[period] <= 3) || Transactions1[Type] = "Forecast 1"
            )
        ),
        "FC2", CALCULATE(
            SUM(Transactions1[balance]), 
            FILTER(
                Transactions1, 
                (Transactions1[Type] = "Actual" && Transactions1[period] <= 6) || Transactions1[Type] = "Forecast 2"
            )
        ),
        "FC3", CALCULATE(
            SUM(Transactions1[balance]), 
            FILTER(
                Transactions1, 
                (Transactions1[Type] = "Actual" && Transactions1[period] <= 9) || Transactions1[Type] = "Forecast 3"
            )
        ),
        BLANK()
    )

FILTER function (DAX) - DAX | Microsoft Learn

Best Regards,

Wisdom Wu

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

[alexando]

Hi @v-jiewu-msft ,

Thank you for the help, that worked fine!

There is still one detail that does not work. The option to "Show data point as table" in the graph was not available after implementing the table and measurement, are there any solutions to being able to get this working?

[v-jiewu-msft]

Hi ,

For show data point as table problem, there are some limitations. If the visual has a measure in the Value field, showing the data underlying a data point will not be available. Try to change the data type to the text type.

You can also view the following documents to learn more information.

Show the data that was used to create the report visual - Power BI | Microsoft Learn

Solved: Columns not appearing in "Show data point as a tab... - Microsoft Fabric Community

Best Regards,

Wisdom Wu

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

[Poojara_D12 · Accepted solution]

Hi @alexando 

Create a Helper Table: Add a table with group names like "A", "B", and "A & B" using this DAX:

TransactionTypeGroups = DATATABLE(
    "Group", STRING,
    { {"A"}, {"B"}, {"A & B"} }
)

Create a Measure: Define a measure to calculate the sum of transaction values based on the selected group:

FilteredTransactionValue =
VAR SelectedGroup = SELECTEDVALUE(TransactionTypeGroups[Group])
RETURN
    SWITCH(
        SelectedGroup,
        "A", CALCULATE(SUM(Transactions[TransactionValue]), Transactions[TransactionType] = "A"),
        "B", CALCULATE(SUM(Transactions[TransactionValue]), Transactions[TransactionType] = "B"),
        "A & B", CALCULATE(SUM(Transactions[TransactionValue]), Transactions[TransactionType] IN {"A", "B"}),
        BLANK()
    )

Add a Slicer: Use the TransactionTypeGroups[Group] column as a slicer to let users select "A", "B", or "A & B".

Build the Bar Chart: Add FilteredTransactionValue to the bar chart's Values field to show dynamic sums based on the slicer selection.

This approach allows users to easily toggle between transaction type groups on the chart.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[alexando]

Thanks, that helped me a lot and gave me the results that I was asking for, it is for a cost followup sheet. However, i found that there is anoterh challenge. I created the helper table as below:

BudgetTypeGroups = DATATABLE(
    "Group", STRING,
    { {"Actual"}, {"Budget"}, {"FC1"}, {"FC2"}, {"FC3"} }
)

and created the measurement as per:

FilteredBudgetValue = 
VAR SelectedGroup = SELECTEDVALUE(BudgetTypeGroups[Group])
RETURN
    SWITCH(
        SelectedGroup,
        "Actual", CALCULATE(SUM(Transactions1[balance]), Transactions1[Type] = "Actual"),
        "Budget", CALCULATE(SUM(Transactions1[balance]), Transactions1[Type] = "Budget"),
        "FC1", CALCULATE(SUM(Transactions1[balance]), (Transactions1[Type] = "Actual"  && Transactions1[period] < 4) || Transactions1[Type] = "Forecast 1"),
        "FC2", CALCULATE(SUM(Transactions1[balance]), (Transactions1[Type] = "Actual"  && Transactions1[period] < 7) || Transactions1[Type] = "Forecast 2"),
        "FC3", CALCULATE(SUM(Transactions1[balance]), (Transactions1[Type] = "Actual"  && Transactions1[period] < 10) || Transactions1[Type] = "Forecast 3"),
        BLANK()
    )

The graph shows correct values when I am looking at the full year but when I want to filter at a speciffic month (Period), the calculated FC1, FC2 and FC3 does not update to show the value for that particular month. It works fine for the Budget and Actual values.

The logics of my data is that e.g. FC1 = actual cost for Jan-Mar and forecasted transactions which are labled Forecast 1 (Apr to Dec).

Is there a solution so the FC sums can be filtered?

I also noticed that the option to "Show data point as table" in the graph was not available any more, is this related to the used method?

[DataNinja777]

Hi @alexando ,

You can achieve what you need by adding a slicer using the Transaction Type column in Power BI. It will filter your bar chart automatically. You can multi-select in the slicer to show combinations like "A & B."

No need for extra measures or complicated setups. The standard slicer does exactly what you're asking for.

Best regards,