Formula with variable references

[Tony5]

I have an Excel report I'm trying to recreate in PowerBI. But I'm stuck at the part where Excel uses match and offset statements to display sumif info based on a selection (see screenshot).

Was wondering if anybody has any idea how I should tackle this in PBI? I can attach the XLSX file. Just not sure where to upload it to.

[Ashish_Mathur]

Hi,

I understand Metric C is an input.  What is the relevance of numbr 3?  Share your Excel file with formulas so i can understand your Excel formula and translate that into DAX.

[Tony5]

That was simply the output of the match formula. Not necessary for display to be honest.
Hmm...i dont think I can upload an attachment via forum. Any advise how I can send/share the Excel file to you?

[Ashish_Mathur]

Upload the file to OneDrive and share the download link.

[Tony5]

Thank you for the tip @Ashish_Mathur . I've uploaded my file to my Onedrive.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Tony5]

Hi Ashish, my apologies for forgetting to mention that I'd prefer to not use unpivoting for the solution.

Reasons is, for the actual file, I'll be working with millions of rows and more than a hundred metrics. Which will compound my unpivot into hundreds of millions of rows.

Do you have any thoughts on a non pivot solution?

[Ashish_Mathur]

Hi,

No.  I do not have any other possible solution.

[Tony5]

Nevertheless, I appreciate your response and assistance Ashish. Thank you very much!

[Ashish_Mathur]

You are welcome.