Forum Discussion

Pepinho18's avatar
Pepinho18
New Member
8 years ago

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. ...
v-yuta-msft's avatar
v-yuta-msft
Community Support
8 years ago

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

Pepinho18's avatar
Pepinho18
New Member
8 years ago

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.