Aggregate Measured Calculation

[Anonymous]

Here is my data source:

What I need to do here is compare different versions.  I did this by duplicating the table 3 times and adding in some filters selections.

The Measure on the far right performs calculations between Table_1_Qtr_1 and Table_2_Qtr_1.  I need to aggregate the Measure to see how many Result 1 there are and how many Results 2 there are.

[v-yingjl]

Hi @Anonymous ,

No matter what is your data look like and not certain how did your measure get result 1&2, I have created a simple table and a measure like this:

Measure_test = 
IF ( SELECTEDVALUE ( test[value] ) <= 2, "Result1", "Result2" )

Create another measure to aggregate measured calculation:

Count = 
VAR tab =
    SUMMARIZE ( 'test', 'test'[value], "Result", [Measure_test] )
VAR newtab =
    ADDCOLUMNS (
        tab,
        "Count",
        VAR tab2 =
            SUMMARIZE ( ALL ( 'test' ), 'test'[value], "Result", [Measure_test] )
        VAR _re = [Result]
        RETURN
            CALCULATE ( DISTINCTCOUNT ( 'test'[value] ), FILTER ( tab2, [Result] = _re ) )
    )
RETURN
    SUMX ( newtab, [Count] )

The sample result will be like this:

You can refer and modify the above measure based on your previous measure as your need.

Attached my sample file that you can refer: Aggregate Measured Calculation.pbix

Best Regards,
Yingjie Li

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

[Anonymous]

My main issue is I need to aggregate by this "Measure_Test" column.  When I remove the value column in your attached file I lose the Measure_Test column.  Any ideas?

[v-yingjl]

Hi @Anonymous ,

In table visual, measure is based on a specific data column dynamically. The value column is like the Product_ID column in your data table, needs it as a reference. If you only put measures in the table visual without any fact column, I don't think you can get the result like 'Result1' and 'Result2'.

If you also have some problems, could you please consider sharing a sample file for further discussion?

Best Regards,
Yingjie Li

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

[amitchandak]

@Anonymous , You need to create a version table twice. And keep it disconnected and use filter in measure

Version1 = distinct(Table[Version])

Version2 = distinct(Table[Version])

You will have a formula like this

Measure =
calculate(sum(Table[Qtr1]), Table[version]=selectedvalue(Version1[Version])) -calculate(sum(Table[Qtr1]), Table[version]=selectedvalue(Version2[Version]))

I have assume disconnected table, so no worry about othe filter/slicer