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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors