DAX (CALCULATE/SUMX) to calculate percentage of category that has reached 100%. Tableau(INCLUDE).

[Jim_PBI]

Hi everyone, 

I'm relatively new to DAX and stuck on a particular problem. I would like to build a gauge chart that shows the percentage of sales agents that have sold all of their products. I have included filters on the page for SalesAgentID, ProductCatgeory and ProductSubCategory. The formula would be something similar to DIVIDE(DISTINCTCOUNT(SalesAgentID (*where SUM(ProductWithAgent)=SUM(ProductSoldByAgent*)),DISTINCTCOUNT(SalesAgentID)). 

It would need to dynamically recalculate with the context of the filter so that only data after the filter is included. I have tried various CALCULATE, SUMX, Calculated Table functions with little success. In Tableau I would have tried a LOD INCLUDE calculation. 

Sample data is below and any help is very much appreciated. 

SalesAgentID	ProductBarcodeID	ProductCategory	ProductSubCategory	ProductWithAgent	ProductSoldByAgent
1	1	Furniture	Chair	1	1
1	2	Furniture	Chair	1	1
1	3	Furniture	Desk	1	1
1	4	Furniture	Bed	1	1
1	5	Tech	Monitor	1	1
1	7	Tech	Keyboard	1	1
2	11	Furniture	Bed	1	0
2	12	Furniture	Bed	1	1
2	13	Furniture	Bed	1	0
3	14	Tech	Monitor	1	1
3	15	Tech	Monitor	1	1

[Fowmy · Accepted solution]

@Jim_PBI 

I think  you wanted this measure:

Sold All Product % = 
DIVIDE(
    SUMX(
        VALUES(Table2[SalesAgentID]),
        INT(CALCULATE(SUM(Table2[ProductWithAgent])) = CALCULATE(SUM(Table2[ProductSoldByAgent])))
    ),
    COUNTROWS(VALUES(Table2[SalesAgentID]))
)

[Jim_PBI]

Thank you very much - exactly what I needed 🙂