Building a table with revenue, different criteria for each category

[unicorns]

Hello,

I'm building out a simple table showing revenue by category, but not sure how to achieve this when I have different revenue criteria for each category (fruits). 

Would I need to build a query/DAX for calculating revenue for each category? 

Data I have: 

Table I'm trying to build:

[ElvirBotic]

@unicorns I would suggest you using the filter context to your advantage. You can modify the DAX measures I gave you and add on the multiple conditions to your measure to filter down and get the result you are looking for. Or you can do it on the modeling side of things where you can create a table for each category type. So, you would have a table for apple and bananas. Or you can build it using one DAX measure and utilizing variables. I would highly suggest you spend some time on dax.guide or sqlbi.com to get more exposure to modeling and dax. If this worked let me know or if I can make it clearer for you. Could you mark it as the solution if you found this to work? 

[ElvirBotic]

@unicorns I was able to build it very easily with summing everything by their coluimn and aggregating into one measure. I created 4 measures to keep it as simple as possible. Let me know if this works for your usecase. Use the matrix table add category to rows, quarter to column, and total revenue measure to the values section. 

 1)

Apple Revenue =

CALCULATE (

    SUM (

        'Category Revenue'[apple rev]

    )

)

2

Banana Revenue =

CALCULATE (

    SUM (

        'Category Revenue'[banana rev]

    )

)
3

Other Revenue =

CALCULATE (

    SUM (

        'Category Revenue'[other rev]

    )

)
4

Total Revenue =

[Apple Revenue] + [Banana Revenue] + [Other Revenue]

[unicorns]

thank you! how would I apply the stage filter? I do need to apply this filter for the actual dataset where quarter 1 and quarter 2 only gets stage=closed revenue. 

[unicorns]

Sorry for the confusion, maybe this helps, I created a new scenario with better 

Data table: 

category	quarter	stage	apple_revenue	banana_rev
Apple	quarter1	closed	10	0
Apple	quarter1	closed	10	0
Apple	quarter1	open	1000	0
Apple	quarter1	open	1000	0
Apple	quarter1	open	30000	0
Apple	quarter2	closed	10	0
Apple	quarter3	open	10000	0
Apple	quarter4	open	10000	0
Banana	quarter1	closed	0	1000
Banana	quarter1	closed	0	1000
Banana	quarter1	open	0	100000
Banana	quarter1	open	0	100000
Banana	quarter2	closed	0	500
Banana	quarter3	open	0	3000
Banana	quarter4	open	0	3000

[ElvirBotic]

You want to SUM the revenues based off of the stage? Just edit your DAX measures to add a filter context. Example...

CALCULATE ( SUM ( Category Revenue'[apple rev] ), Category Revenue[Stage] = "Closed" )

But if you want it for just your dataset then I would create a custom column where I concat quarter and stage and use that column to add it to my DAX measure. Let me know if that makes sense. I am sure there are much more efficient ways of doing that. 

[grantsamborn]

Hi @unicorns 

Would totalling all of the categories in one measure help? 

Revenue = 
    SUM( 'DataTable'[apple_rev] )
        + SUM( 'DataTable'[banana_rev] )
        + SUM( 'DataTable'[others_rev] )

Then you can let the Row and Column filtering do the rest.

Hopeffully I'm not misunderstanding your requirements.

pbix: unicorns.pbix

[unicorns]

looks like it may not work since I need to apply filters as well (stage/quarter) and each category is needing to pull revenue from different data columns but I need to put all categories revenue in one table, and each category is using different revenue column, and need to have filters applied for stage/quarter. 

[grantsamborn]

Hi @unicorns 

Is there any you can provide the following?

a) *sample* data reflecting your source tables

b) your expected results

c) a description of how you plan to get from a) to b).

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[unicorns]

Hello @grantsamborn, 

Here is the sample PBI file for your reference: sample_pbi.pbix

Here is the expected result. Each category's revenue for each quarter has specific filters I need to apply, and each category uses different column revenues (ex. Apple has revenue from "apple_revenue" column and Banana should only have revenues added from "banana_rev).

[grantsamborn]

Hi @unicorns 

I'm not sure I see the difference with my pbix other than my measure.

A couple of questions in response to your earlier post:

"looks like it may not work since I need to apply filters as well (stage/quarter) and each category is needing to pull revenue from different data columns but I need to put all categories revenue in one table, and each category is using different revenue column, and need to have filters applied for stage/quarter. "

- Does all of your source data come from a single query or multiple queries/tables?

     - Does your model include dimension tables?

     - Are you using a date table or just the [quarter] column?

- Besides [category] and [quarter] (also, [stage]), are there other filters?

- In the screen shot, I'm not sure what you mean by "2. quarter1 & quarter2 should be stage = closed".

     - I originally guessed that it meant that rows with a [stage] of closed shouldn't be included.  Your expected results show the opposite.

I'm hoping this makes sense.

[unicorns]

Sorry for the confusion, maybe this helps, I created a new scenario with better 

Data table: 

category	quarter	stage	apple_revenue	banana_rev
Apple	quarter1	closed	10	0
Apple	quarter1	closed	10	0
Apple	quarter1	open	1000	0
Apple	quarter1	open	1000	0
Apple	quarter1	open	30000	0
Apple	quarter2	closed	10	0
Apple	quarter3	open	10000	0
Apple	quarter4	open	10000	0
Banana	quarter1	closed	0	1000
Banana	quarter1	closed	0	1000
Banana	quarter1	open	0	100000
Banana	quarter1	open	0	100000
Banana	quarter2	closed	0	500
Banana	quarter3	open	0	3000
Banana	quarter4	open	0	3000