Replacing Total With Difference to Compare values between factors.

[EHa]

Hello All!

Please let me know / point me toward a solution if this has been asked elsewhere, but I am trying to create a report where the user can select runs to compare ( currently using a chiclet slicer) , and then use slicers / dropdowns to filter through the other data categories , which are displayed in a matrix view on the report.

Hopefully some example screenshots will make this clear 

Note - my real data will be imported using SQL direct query from large datasets.

I wish to compare only two runs at a time, and replace the standalone total column on the right with one which instead shows the difference between the two runs selected ( This should be filtered by the data selcted with the dropdowns)

Hope someone can help,

Thanks a lot,

EHa

[EHa]

Hi Guys,

Thanks a lot for your answers and constant contribution to the forum.

I haven't had time to properly experiment yet, but I am having an issue with my example which I don't think can be sorted in matrix visualisation, but would appreciate your inputs.

As you can see, when I created my difference measure , it works pretty well , but when I add it to the values field, it clutters my whole table up with useless data.  Is there a way to get it to just appear once ( instead of the total at the end )? 

The only method I can think of is to seperate my data to have a different measure for each selected run, and show them all side by side in a table, but then my table could update with more runs and leave my report broken.

Thanks a lot,

EHa

[EHa]

Oops, forgot to add an image - this screenshot should make it more clear - 

[v-diye-msft]

Hi @EHa 

For the question" Is there a way to get it to just appear once ( instead of the total at the end )? "

Did you try to disable the subtotal?

[EHa]

Hi @v-diye-msft ,

Thanks for the reply. If I turned off those subtotals it would turn off the subtotal for the "difference" measure, and the only part of the difference measure I wanted was the total accross the columns. 

The method I ended up using was to turn off word wrap and manual resize the difference columns to be as small as possible for the standard run columns, and to resize the value column to be as small as possible for the column subtotal. This just about works for my current data but will fail when new columns are added for new runs, and are automatically resized to fit the data.

Any ideas for a work around?

EHa

[Greg_Deckler]

@EHa - This has been coming up quite a bit lately, check out Table (15), Page 15 of the attached PBIX file attached below sig. The solution is basically the following, this one was comparing averages:

Measure 5 = 
    VAR __Average = AVERAGE('Table (15)'[Test Score])
RETURN
    IF(HASONEVALUE('Table (15)'[Weekending Date]),__Average,
            VAR __Min = MIN([Weekending Date])
            VAR __Max = MAX([Weekending Date])
            VAR __AverageMin = AVERAGEX(FILTER('Table (15)',[Weekending Date]=__Min),[Test Score])
            VAR __AverageMax = AVERAGEX(FILTER('Table (15)',[Weekending Date]=__Max),[Test Score])
        RETURN
            __AverageMin - __AverageMax
    )

[amitchandak]

@EHa , Try a measure like this for diff

diff =
var _min = minx(allselected(Table), Table[Run])
var _max = maxx(allselected(Table), Table[Run])
return
calculate(sum(Table[Value]), filter(Table,Table[Run] =_max)) -calculate(sum(Table[Value]), filter(Table,Table[Run] =_min))