Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
My data is organized in four tables.
And this is my tables' data
Than I created this table:
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.
Solved! Go to 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])
Hi
I hope the below Dax Function will help you.
Dax Function: Comission = Sheet1[Value]*Sheet1[Comission Percentage] (New Column)
@hthota, the problem is that the columns are not on the same table. And both table are not relationed with each other.
Assuming Commission is a measure, perhaps:
MyCommission = IF (HASONEFILTER(Table[Year]), [Commission], CALCULATE([Commission],ALLSELECTED(Worker)) )
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.
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.
@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])
Awesome!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |