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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Super User
Super User

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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