Grand Total of measure doesn't equal sum of row values

[Anonymous]

Hello,

My data is organized in four tables.

And this is my tables' data

Worker

Product

Commission

Sales

Than I created this table:

Commission By Worker and Product

Depeding of the worker and the produts it sells, the worker receives a commission. For that I create the following measure:

Commision = SUM(Sales[Value]) * SUM(ComissionByProductAndDAy[CommisionPercentage])

It is working, but the only problem is the total ammount of commission. The value must be  21 + 120 + 10 + 105 = 256. I know why it is happenig. I am trying to follow @Greg_Deckler tutorial, about dealing with measure totals - http://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#U63376

But I do not known how to adapt his solution to mine problem. Can someone help me?

Thanks. 

[hthota]

Hi 

I hope the below Dax Function will help you.

Dax Function: Comission = Sheet1[Value]*Sheet1[Comission Percentage]  (New Column)

[Anonymous]

@hthota, the problem is that the columns are not on the same table. And both table are not relationed with each other.

[User_PBI]

Hello,

Please use the DAX function calculate, this will resolve the issue.

Thanks.

[Anonymous]

@User_PBI, can you show us how, please?

[Greg_Deckler]

Assuming Commission is a measure, perhaps:

MyCommission  = 

IF  (HASONEFILTER(Table[Year]),
  [Commission],
  CALCULATE([Commission],ALLSELECTED(Worker))
)

[Anonymous]

@Greg_Deckler,

It did not work. The total value continues the same. I alredy tried to ALLSELECTED for Product and Both - Product and Worker. 

Commission = 
VAR
    _commission = SUM(Sales[Value]) * SUM(ComissionByProductAndDAy[CommisionPercentage])
RETURN
    IF(
        HASONEFILTER(Worker[Name]),
        _commission,
        CALCULATE(_commission, ALLSELECTED(Worker))
    )

But  it keeps calculating seventy percent of 1260 that is equals 882. 

[Greg_Deckler]

What about:

MyCommission  = 

IF  (HASONEFILTER(Table[Year]),
  [Commission],
  CALCULATE([Commission],ALLSELECTED())
)

? If that doesn't work, you can always do a SUMMARIZE just as you are doing your table visualization and then just take the SUM of your Commission column in your SUMMARIZE table. 

[Anonymous · Accepted solution]

@Greg_Deckler, it not worked using ALLSELECTED(), but your idea to use SUMMARIZE worked. 

This is what I did:

Commission = 
VAR
    _commission = SUM(Sales[Value]) * SUM(ComissionByProductAndDAy[CommisionPercentage])
RETURN
    SELECTCOLUMNS(SUMMARIZE(Sales, 'Product'[ProductID], Worker[Name], "COMMISSION", _commission), "CNAME", [COMMISSION])

[Greg_Deckler]

Awesome!