cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Tony5 Frequent Visitor
Frequent Visitor

Formula with variable references

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.

 

smartdash.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Formula with variable references

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

9 REPLIES 9
Super User
Super User

Re: Formula with variable references

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

Re: Formula with variable references

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?
Super User
Super User

Re: Formula with variable references

Upload the file to OneDrive and share the download link.

Tony5 Frequent Visitor
Frequent Visitor

Re: Formula with variable references

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

Super User
Super User

Re: Formula with variable references

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

Tony5 Frequent Visitor
Frequent Visitor

Re: Formula with variable references

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?

Super User
Super User

Re: Formula with variable references

Hi,

 

No.  I do not have any other possible solution.

Tony5 Frequent Visitor
Frequent Visitor

Re: Formula with variable references

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

Super User
Super User

Re: Formula with variable references

You are welcome.