Forum Discussion

mcmullenrich's avatar
mcmullenrich
Frequent Visitor
4 years ago
Solved

Calculate Variance Between Two Columns in Matrix Visual

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 th...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

mcmullenrich's avatar
mcmullenrich
Frequent Visitor
4 years ago

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.

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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