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
Pepinho18
New Member

Measure Visualization Issue - Grand Total in Table doesn't match Row

Hi all,

 

Not sure if I have one or more issue with what I am trying to do here but essentially I am using a Measue to do some FX Scenario Analysis by utlising the Visualisation Table and Slicers. The issue I am having with the Measure is that the Grand Total in Table Visualisation does not match the sum of all the rows. I have created this Measure as a Calculated Column for reconciliation purposes, but I need to use the Measure function to allow fo dynamic selection of Scenarios using the slicer. My formula is as follows:

 

Net Sales Re-Statement3 = VAR selection = SELECTEDVALUE('FX Rates'[YearScen]) VAR selection2 = SELECTEDVALUE('PRISMA Data'[YearScen]) VAR MCcurrency = LOOKUPVALUE(Markets[Currency],Markets[PRISMA MARKET 1],FIRSTNONBLANK('PRISMA Data'[PRISMA MARKET 1],'PRISMA Data'[PRISMA MARKET 1]))
RETURN
SWITCH(TRUE(),
Selection = "A17",CALCULATE(SUMX('PRISMA Data','PRISMA Data'[Net sales (ex TD) total]),FILTER('PRISMA Data','PRISMA Data'[YearScen] = selection2 && 'PRISMA Data'[PRISMA BRAND] <> "MISC_WINES" && 'PRISMA Data'[PRISMA BRAND] <> "WINES_BULK")) * LOOKUPVALUE('FX Rates'[FX Rate EUR],'FX Rates'[YearScen],Selection,'FX Rates'[Currency],MCcurrency) + CALCULATE(SUMX('PRISMA Data','PRISMA Data'[Net sales (ex TD) total]),FILTER('PRISMA Data','PRISMA Data'[YearScen] = selection2 && 'PRISMA Data'[PRISMA BRAND] = "MISC_WINES" || 'PRISMA Data'[PRISMA BRAND] = "WINES_BULK")),
Selection = "A16",CALCULATE(SUMX('PRISMA Data','PRISMA Data'[Net sales (ex TD) total]),FILTER('PRISMA Data','PRISMA Data'[YearScen] = selection2 && 'PRISMA Data'[PRISMA BRAND] <> "MISC_WINES" && 'PRISMA Data'[PRISMA BRAND] <> "WINES_BULK")) * LOOKUPVALUE('FX Rates'[FX Rate EUR],'FX Rates'[YearScen],Selection,'FX Rates'[Currency],MCcurrency) + CALCULATE(SUMX('PRISMA Data','PRISMA Data'[Net sales (ex TD) total]),FILTER('PRISMA Data','PRISMA Data'[YearScen] = selection2 && 'PRISMA Data'[PRISMA BRAND] = "MISC_WINES" || 'PRISMA Data'[PRISMA BRAND] = "WINES_BULK")),
Selection = "B18",CALCULATE(SUMX('PRISMA Data','PRISMA Data'[Net sales (ex TD) total]),FILTER('PRISMA Data','PRISMA Data'[YearScen] = selection2 && 'PRISMA Data'[PRISMA BRAND] <> "MISC_WINES" && 'PRISMA Data'[PRISMA BRAND] <> "WINES_BULK")) * LOOKUPVALUE('FX Rates'[FX Rate EUR],'FX Rates'[YearScen],Selection,'FX Rates'[Currency],MCcurrency) + CALCULATE(SUMX('PRISMA Data','PRISMA Data'[Net sales (ex TD) total]),FILTER('PRISMA Data','PRISMA Data'[YearScen] = selection2 && 'PRISMA Data'[PRISMA BRAND] = "MISC_WINES" || 'PRISMA Data'[PRISMA BRAND] = "WINES_BULK")), "YOU WRONG!")

 

My table results based on A16 re-statement, the A16 Re-statement is the Calculated Column to which I am reconciling to (and has the correct total amount). As you can see the Net Sales Re-Statement3 column does not match, despite all row totals being exactly the same...

 

Untitled.jpg

 

I know this has to do with the FIRSTNONBLANK function in my variable but unsure how else to get my LOOKUPVALUE to work. Any help would be greatly appreciated!

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

Hi Pepinho18,

 

As a general suggestion, measure returns a aggregation value by iterating all rows in table for one time however calculate colmn will aggregate all row in aggregation column for more than one time. That's the difference. In addtion, could you give more details about calculate column [Net Sales Re-Statement] for further analysis if you still don't understand?

 

Regards,

Jimmy Tao

Thanks for the response Jimmy, that definitely expalins the variance. My Re-statement caclulated column (for A16) looks like this:

 

IF('PRISMA Data'[PRISMA BRAND]="MISC_WINES" || 'PRISMA Data'[PRISMA BRAND]="WINES_BULK", ('PRISMA Data'[Net sales (ex TD) total]),('PRISMA Data'[Net sales (ex TD) total] * LOOKUPVALUE('FX Rates'[FX Rate EUR],'FX Rates'[YearScen],"A16",'FX Rates'[Currency],RELATED(Markets[Currency]))))

Obviously I repeat columns for every period (A17, A15 etc). 

 

So as I understand it, FIRSTNONBLANK function is essentially bringing back an aggregated rate at total level? 

 

Is there anyway you can see I may tweak my measure to get the right Total amount? As my Director wants interactive visualisation to monitor these re-statements, numerous calculated columns isn't really an option. 

 

Happy to share my data with you through a private message. 

 

 

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.