Forum Discussion

vibhoryadav23's avatar
vibhoryadav23
Helper II
1 year ago
Solved

Data modelling suggestions

I have a dataset in which the base of my calculations dynamically changes based on various filters. Some of these filters can directly be applied but some of these are coming from other tables which ...
General Comment
Need Help
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi vibhoryadav23 - you can write measures to dynamically calculate the percentage of each error while considering the applied filters.

    create total count of Errors

    Total Errors =
    CALCULATE(
    COUNT('Table'[Error]),
    ALL('Table'[Error]) -- Ensures all errors are considered, even if some are filtered
    )

     

    create percentage of Each Error

    Error % =
    VAR CurrentErrorCount =
    COUNT('Table'[Error])
    RETURN
    DIVIDE(CurrentErrorCount, [Total Errors], 0) * 100

     

    Create a measure that applies the selected scenario dynamically

    Scenario Filter =
    SWITCH(
    SELECTEDVALUE('Scenario Table'[Scenario]),
    "A", 1, -- No filter applied
    "B", IF('Table'[TNr] = 1, 1, 0),
    "C", IF('Table'[TNr] = 2 && 'Table'[RT] = 1, 1, 0),
    "D", IF('Table'[TNr] >= 2 && 'Table'[RT] = 1, 1, 0),
    1 -- Default to no filter
    )

     

    To exclude "OK" values from the chart while keeping them in calculations

    Error % for Chart =
    IF(
    MAX('Table'[Error]) = "OK",
    BLANK(), -- Exclude "OK" from the chart
    [Error %] -- Show percentage for other errors
    )

     

    Use a bar chart to display the percentages for each error.

    Set the Error field on the X-axis.
    Use the Error % for Chart measure as the Y-axis value.
    Add a slicer for the Scenario Table so users can select the desired scenario.

    Add any other slicers (e.g., for Date, ID, etc.) to allow users to filter the data.

     

    Hope this works. please try

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi vibhoryadav23 

 

You can try using the ALLEXCEPT() function to keep the filters you want in the table.

1. Create a calculated column to represent the different conditions:

Condition = SWITCH(
    TRUE(),
    'Table'[TNr] = 1, "B",
    'Table'[TNr] = 2 && 'Table'[RT] = 1, "C",
    'Table'[TNr] >= 2 && 'Table'[RT] = 1, "D",
    BLANK()
)

Create a slicer with calculated columns, check the Select All option in the slicer settings and apply “is not blank” in the visual filter panel. "Select all” means condition A.

2. Create a measure to calculate the error message percentage:

(If you have slicers for other fields, add the appropriate fields to the ALLEXCEPT() function body.)

Error % = 
VAR TotalErrors = CALCULATE(
    COUNT('Table'[Error]), 
    ALLEXCEPT('Table', 'Table'[Date], 'Table'[Condition])
)
RETURN
SUMX(
    VALUES('Table'[Error]),
    VAR CurrentError = [Error]
    VAR ErrorCount = CALCULATE(
        COUNT('Table'[Error]), 
        'Table'[Error] = CurrentError,
        ALLEXCEPT('Table', 'Table'[Date], 'Table'[Condition])
    )
    RETURN
    IF(
        CurrentError <> "OK",
        DIVIDE(ErrorCount, TotalErrors),
        BLANK()
    )
)

 

 

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