Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mcmullenrich
Frequent Visitor

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 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.

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
mcmullenrich
Frequent Visitor

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.

 

mcmullenrich_0-1649740523069.png

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

vojtechsima
Memorable Member
Memorable Member

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.