% of Volume By Agent, Type, and Time Frame

[Anonymous]

I can't quite figure out a DAX formula that would help me figure out the following.

I have the following data laid out as such.

The Calc is a number.

The Type is a line item data label.

The Rep is a sales rep.

And the date, for this is example is set to Quarters in a year.

I'm trying to "weave" in and out of the data and look at Per Rep Volume of sales, based on the sales category and Date...

It's not the % of the grand total, but the % based on Date, Person, and Sales.

Calc	Type	Rep	Date
7	Sales	Joe	Q1
46	Hours	Joe	Q1
12	Sales	Paul	Q1
45	Hours	Paul	Q1
14	Sales	Frank	Q1
41	Hours	Frank	Q1
8	Sales	Frank	Q2
30	Hours	Frank	Q2
4	Sales	Joe	Q2
32	Hours	Joe	Q2
3	Sales	Paul	Q2
20	Hours	Paul	Q2
6	Sales	Paul	Q3
35	Hours	Paul	Q3
2	Sales	Frank	Q3
36	Hours	Frank	Q3
9	Sales	Joe	Q3
32	Hours	Joe	Q3

The desired result is the following:

	Q1	Q2	Q3
Frank	42.42%	53.33%	11.76%
Joe	21.21%	26.67%	52.94%
Paul	36.36%	20.00%	35.29%

I'm stumped.  I've tried different DAX using Calculate, Sum, and Filter, but it still gives me an error message.

Any help would be GREATLY apprec

[ryan_mayu · Accepted solution]

@Anonymous 

please try this

Measure = 
VAR sales=CALCULATE(SUMX(FILTER('Table','Table'[Type]="Sales"),'Table'[Calc]),ALLEXCEPT('Table','Table'[Rep],'Table'[Date]))
VAR total=CALCULATE(SUMX(FILTER('Table','Table'[Type]="Sales"),'Table'[Calc]),ALLEXCEPT('Table','Table'[Date]))
return DIVIDE(sales,total)

[Anonymous]

@ryan_mayu 

Thanks Ryan..  I think it's close, but I may need to tweak it a bit based on how I really have the data laid out.

I'm using 3 tables built as relationships between them for ease of data use.

The Sales table which has the sales info.

A Rep Table, that has the rep information.

and a Date table that has different variations of dates and time frames such as Quarter, Month, Year, etc.

I'm using those 3 to converge everything together.

So I've got the majority of the DAX code you supplied set up... I'm kind of stuck now bringing in the Date part for the ALLEXCEPT part, being that DATE is a separate table.  From what it looks like, from what I can see, the AllExcept can only use one table, so what if I'm using 2 tables, in my case of Date and Rep tables?

[ryan_mayu]

@Anonymous

I think allexcept can be used for 2 tables. allexcept(table1, table1[column],table2[column]), you can have a try.