Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi All,
I have a table like this, and I want to calculate both turnover % (total) and turnover% by region only. My desired outcome is as follow:
Region & Product | Turnover Amount | Turnover % Total | Turnover % Region |
Region : A | 110 | (110/260) = 42.30% | 100% |
Product: 1 | 50 | (50/260) = 19.23% | (50/110) = 45.45% |
Product : 2 | 30 | (30/260) = 11.54% | (30/110) = 27.27% |
Product: 3 | 30 | (30/260) = 11.54% | (30/110) = 27.27% |
Region: B | 150 | 57.70% | 100% |
Product:1 | 70 | 26.93% | (70/150) = 46.66% |
Product: 2 | 60 | 23.07% | (60/150) = 40% |
Product: 3 | 20 | 7.69% | (20/150) = 13.33% |
TOTAL | 260 | 100% |
However, it seems like my measure is not working. If I filter any region/product, the percentage value will change.
My measure:
Solved! Go to Solution.
HELLO, I think I found the solution!
Basically what I expected is when user select certain monthyear, they can see turnover details for that monthyear by region & product, like attached:
However, if I use ALLSELECTED(FactTable), and I filter certain Region slicer, the value of % Total here is changed, like example below:
But, if I use ALL(FactTable), the value is weird as the total is not 100%, as currently I am selecting Date Mar 2022.
So, now I am using ALLCROSSFILTERED(FACTTable) and seems like it solve the problem!
Thank you so much for helping and giving ideas! Really appreciate it!
Try these measures:
Turnover Amount = SUM ( vw_FactTable[TurnoverAmt] )
Turnover % Total =
VAR vNumerator = [Turnover Amount]
VAR vDenominator =
CALCULATE ( [Turnover Amount], ALLSELECTED ( vw_FactTable ) )
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult
Turnover % Region =
VAR vNumerator = [Turnover Amount]
VAR vDenominator =
CALCULATE (
[Turnover Amount],
ALLSELECTED ( vw_FactTable ),
VALUES ( vw_FactTable[Region] )
)
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult
Proud to be a Super User!
hi @DataInsights ,
Currently if i use this method, if I filter Product,
let say I only select Product 1 & 2, the % total & region will be changed.
But I want the result/figure to be the same as before they filter.
Can you please help? Maybe you can share your pbi file here.
Thank you!
If you want the totals to ignore the filter, replace ALLSELECTED with ALL.
Proud to be a Super User!
Hi @DataInsights ,
I have tried the method, but the total doesnt come out as 100% as I wanted. The total is 3.90%. So, I think this is not correct as well.
I think it is because it also unfilter date, whereas my calculation is done for monthly basis. I have tried ALLEXCEPT(FactTable, FactTable[Date]) but it also convert to me 3.90% result as well.
Do you have any idea on what are the other measure should i use?
Loking forward to your reply!
Would you be able to provide the expected result with only Products 1 and 2 selected? The format in the original post is great because it shows how each percentage is calculated. Also provide a screenshot of your data model or a sample pbix.
Proud to be a Super User!
Hi @DataInsights ,
The result I need is same as the one you first provided, and the figure should not changed when user filter Region / Product name.
My relationship is like this table:
In Date table, I also have MonthYear column which like this format --> January 2022, February 2022....
Date is connected to Fact table via DateKey. Product is connected to Facttable via ProducrKey. In my fact table, 4 main column important are datekey, region, productkey and datekey.
As for sample, I am so sorry I cant give because of confidentiality. Perhaps you can share me your pbix file, I try to add on the details.
How do I solve this? Please help.
Thanks for the data model screenshot. I need clarification regarding "the figure should not change when user filter Region / Product". You can provide the example in the same format as the original data. An Excel mock-up is fine.
Proud to be a Super User!
HELLO, I think I found the solution!
Basically what I expected is when user select certain monthyear, they can see turnover details for that monthyear by region & product, like attached:
However, if I use ALLSELECTED(FactTable), and I filter certain Region slicer, the value of % Total here is changed, like example below:
But, if I use ALL(FactTable), the value is weird as the total is not 100%, as currently I am selecting Date Mar 2022.
So, now I am using ALLCROSSFILTERED(FACTTable) and seems like it solve the problem!
Thank you so much for helping and giving ideas! Really appreciate it!
So sorry, when I tried to use ALLCROSSFILTERED, the data is not changed but, the amount calculated is wrong. I didnt notice this before. So ALLCROSSFILTERED also wrong. Not sure what ar ethe other function i can try?
Please help!
User | Count |
---|---|
94 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
143 | |
112 | |
73 | |
55 |