Evaluate logic through separate table

[10500438]

So I have a very large data set, I have one year of data in my model and its already making my PBIx file 651MB.

One of the issues I face is this:

I have Sales Revenue Type description that describes my Revenue. For Example Coating Revenue, Grade Revenue, Pickling Revenue, Quality Revenue, Size Revenue, etc. Every sales has revenue in each category. My file originally looks like this:

I use Power Query to unpivot the columns so that I get one column Called "Sales Revenue Type" that looks like this and I use this file in Power BI.

The reason I do this is because I want to create visuals that have descriptive lines for each type of Sales Revenue. So I use the Sales Revenue Type as the "Category" in the visual. See below.

Is there a way to achieve this without unpivoting the columns. Unpivoting the columns makes most of the files too large for one excel worksheet and I end having to split the files into multiple worksheets. 

I know that you can create a standalone table in power bi that you can run logic through to evaluate data, but I couldn't figure out how to connect the column data to a table that and it evaluate on row basis. Ideally I would like to have a table with a row for each sales revenue type and it evaluates (calculates) how much revenue should be in each row by the row description.

Any thoughts?

[kentyler · Accepted solution]

"I know that you can create a standalone table in power bi that you can run logic through to evaluate data, but I couldn't figure out how to connect the column data to a table that and it evaluate on row basis. Ideally I would like to have a table with a row for each sales revenue type and it evaluates (calculates) how much revenue should be in each row by the row description."
You are correct that you will need to load the data into power bi

Then unpivot your columns

Then make a "reference" copy of the table and remove all the columns but Sales Revenue Type, then set it to remove duplicates

Then you can base a report graphic on the sales revenue type table, and write a measure to calculate the revenue by type. When you place the measure in the graphic it should give you something like your illustration.

Join the conversation at WeTalkBi.com
Learn the benifits of practicing "slow" BI at SlowBi.com

[10500438]

Thank you for taking the time to answer my question. I appreciate it very much!