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
ariana_night
Frequent Visitor

Show Variance between 2 columns set as % of grand total

Hello,

 

In my matrix, I need to show (in a separate column to the right of the last column) the variance between "Contribution to XS" and "FL % of TTL INV". Contribution to Excess - FL % of TTL INV  is not an option because "Contribution to XS" and "FL % of TTL INV" are the same fields as the first 2 columns you see below [FL INV] and [FL XS INV], but set as % of grand total because that is what I need shown. 

I appreciate any help with this.

 

ariana_night_0-1661361670140.png

 

1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

The clarification was great, thank you.

I replicated the portion of the data you shared into a table with an index column to get

jgeddes_0-1661372715193.png

I then wrote measures for FL XS RATE 

FL XS Rate =
DIVIDE(SUM(FL_INV_TABLE[FL XS INV]),SUM(FL_INV_TABLE[FL INV]))
Contribution to XS
Contribution to XS =
DIVIDE(
    SUM(FL_INV_TABLE[FL XS INV]),
    CALCULATE(SUM(FL_INV_TABLE[FL XS INV]),ALL(FL_INV_TABLE[Index]))
)
FL % of TTL INV
FL % of TTL INV =
DIVIDE(
    SUM(FL_INV_TABLE[FL INV]),
    CALCULATE(SUM(FL_INV_TABLE[FL INV]),ALL(FL_INV_TABLE[Index]))
)
And finally the variance as
Variance =
[Contribution to XS] - [FL % of TTL INV]
I end up with
jgeddes_1-1661372945694.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
jgeddes
Super User
Super User

The clarification was great, thank you.

I replicated the portion of the data you shared into a table with an index column to get

jgeddes_0-1661372715193.png

I then wrote measures for FL XS RATE 

FL XS Rate =
DIVIDE(SUM(FL_INV_TABLE[FL XS INV]),SUM(FL_INV_TABLE[FL INV]))
Contribution to XS
Contribution to XS =
DIVIDE(
    SUM(FL_INV_TABLE[FL XS INV]),
    CALCULATE(SUM(FL_INV_TABLE[FL XS INV]),ALL(FL_INV_TABLE[Index]))
)
FL % of TTL INV
FL % of TTL INV =
DIVIDE(
    SUM(FL_INV_TABLE[FL INV]),
    CALCULATE(SUM(FL_INV_TABLE[FL INV]),ALL(FL_INV_TABLE[Index]))
)
And finally the variance as
Variance =
[Contribution to XS] - [FL % of TTL INV]
I end up with
jgeddes_1-1661372945694.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





jgeddes
Super User
Super User

Hello,
I do not fully understand what you are looking for but if you can share the desired results for at least the first couple of rows I may be able to assist.

Cheers.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Here's an example in excel (see below): basically =F2-G2 

but it is not that simple in PowerBI desktop, unless I do not think it is. See explanation after screenshot.

ariana_night_0-1661371642064.png

 

The column above "Contribution to XS" is a field called 'Excess Inventory' [FL XS INV] and after renaming it for the visual, I set the field as % of grand total see image below: (same goes for the "FL % of TTL INV" column in the image above). 

ariana_night_1-1661371780588.png

I'm not too sure how to create a measure to give me the desired result (shown in column H of first image). 

 

Please let me know if I am still unclear or need to provide additional information. 

 

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.