Measure calculates incorrectly when columns are not shown in the table

[mariov]

I have an issue with measure which is calculating correctly only when columns it's retrieving the values from are shown in the table.

I'm using measure to calculate table values (Availability Avg) and measure that's used as a filter (AvailabilityFilter, to filter table based on a slicer value (Include System Value, boolean)).

E.g. with table design below I'm getting expected total but duplicate rows:

If I remove Boundary and Type columns from the table I'm getting incorrect rows and total (AvailabilityFilter is returning blanks):

Measures:

Availability Avg = 100 - (SUM([Availability (Hrs)]) / 24 / 3.65)

AvailabilityFilter = 
IF(
    AND(
        OR(  
            AND(
                MIN([Boundary]) = "System", 
                [Include System Value]
            ),
            MIN([Boundary]) = "Package"
        ),
        OR(
            MIN([Type]) = "Planned", 
            MIN([Type]) = "Unplanned"
        )
    ),
    "Show"
)

Any help greatly appreciated

[PaulDBrown]

@mariov 

See if this works for you:

1) Create a measure to filter the table to get the rows:

Filter "Show" =
COUNTROWS (
    SUMMARIZE (
        FILTER ( Sheet1, [AvailabilityFilter] = "Show" ),
        Sheet1[Equipment Description],
        Sheet1[Boundary],
        Sheet1[Type]
    )
)

2) create a measure to get the average of the filtered rows:

Average by Eq. Description =
AVERAGEX (
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( Sheet1, [Filter "Show"] = 1 ),
            Sheet1[Equipment Description],
            Sheet1[Boundary],
            Sheet1[Type]
        ),
        "@Aver", [Availability Avg]
    ),
    [@Aver]
)

And the result:

I've attached the sample PBIX for your reference

[mariov]

Hi @PaulDBrown,

Thanks for this - it looks close to what I need. Just need to fix the values showing in Average by Eq. Description. I need these values showing when Include System slicer is set to True:

Not sure how to accomplish this with your measures so greatly appreciate if you could help.

It seems you're calculating average only for rows that have "Show" in AvailabilityFilter column but I need to apply this logic for each Equipment Description group: 

100 - (SUM([Availability (Hrs)]) / 24 / 3.65)

Which means for each Equipment Description group I need to calculate sum of (Availability (Hrs) / 24 / 3.65) and subtract this sum from 100. Each Equipment Description group will have the same result in that case and there will be no duplicates.

If Include System is set to True I need to calculate based on all rows in the table. If it's False the calculation should only consider rows where AvailabilityFilter is blank (<> "Show", "Show" means "System").

Sorry it's complicated! Still learning DAX 😞

[v-yangliu-msft]

Hi  @mariov ,

Power bi will automatically de-duplicate completely repeated columns. If not, it means that some columns are not repeated. For example, the following picture:

The column [Equipment Description] is duplicated. In theory, it is deduplicated into one, but the latter two columns [Boundary] and [Type] are not the same, so they will be kept.

If you only want to keep one, you need to provide the next logic, how to deal with [Availability Avg], whether to aggregate together, or just take one of them

Best Regards,

Liu Yang

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

[v-yangliu-msft]

Hi  @mariov ,

The two fields [Boundary] and [Type] are used in this measure. If you put only one [Equipment Description] as a field, and all the others are meausre, then only this field will be grouped and calculated, resulting in grouping errors. , So these two fields are indispensable conditions

If you don’t want to see these two fields, you can set them to white,

Select the field in Field formatting, set both Font color and background color to white, and set Apply to header to on

Result:

Best Regards,

Liu Yang

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

[mariov]

Hi,

Thanks so much for your reply. However, the issue I have if I leave [Boundary] and [Type] columns in the table is I'm getting duplicates in Equipment Description column. Any suggestions on how to eliminate duplicates while still keeping the calculations correct?

Cheers

[amitchandak]

@mariov , You are using row context, Min of categorical values in 2nd measure. And in that case, if they are not present in the context you might not get the correct number

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[mariov]

Hi,

Thanks for the response. Here is the sample file.

https://easyupload.io/1ebr88 

[PaulDBrown]

@mariov 

The file is not available in the link you provided

[mariov]

Hi,

I updated the link.

Thanks