Calculate Variance Between Two Columns in Matrix Visual

[mcmullenrich]

I have two fact tables: f_scripts and f_rebates. Each has a quantity column showing sum of units by date (I also have them linked to a calendar table). I'm trying to create a matrix visual showing the number of units by product from f_scripts in one column and the same thing from f_rebates in the next column (I've accomplished this part). The last part is to create a column showing the delta between the two.

If it's helpful, I could try to create a dummy file to share, but the live file has a ton of sensitive info in it.

[mcmullenrich]

Hi @vojtechsima. Here is a snapshot of the visual as it stands now. The two columns shown come from different tables. I'd like to have a column to the right of "Purchased" showing [Purchased] - [Pharmacy]. This visual is controlled by a Month Year dropdown slicer which comes from my calendar table.

[v-shex-msft · Accepted solution]

HI @mcmullenrich,

What type of aggregate that you want to apply to these grouped ranges of records? Can you please share some more detail?

How to Get Your Question Answered Quickly 

Here is a sample measure formaul about average of records based on current hierarchy group.

formula =
CALCULATE (
    AVERAGE ( Table[Purchased] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Common Drag Name] ),
    VALUES ( Table[Pharmacy] )
)

Regards,

Xiaoxin Sheng

[vojtechsima]

Hi,
@mcmullenrich please share a dummy copyable dataset of your data.

[vojtechsima]

Hi, @mcmullenrich 
If you could share some sample data or help me picture how exactly the delta should be shown (absolute, relative etc.) 
Thank you