cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

## Calculate Percentage by Region & Product

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:

Gross Turnover % Total =
var ttlGT =
CALCULATE(-SUM(vw_FactTable[TurnoverAmt]),ALLSELECTED('vw_FactTable'))
RETURN
DIVIDE([Turnover],ttlGT,0)

But, if I changed ALLSELECTED to ALL, I also get weird amount, where the % is 0 even thouh there is amount for turnover, and  my total percentage would not be 100%.

How do I create these measure? Kindly help!
1 ACCEPTED SOLUTION
Resolver I

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!

9 REPLIES 9
Super User

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!

Resolver I

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.

Thank you!

Super User

If you want the totals to ignore the filter, replace ALLSELECTED with ALL.

Proud to be a Super User!

Resolver I

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?

Super User

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!

Resolver I

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.

Super User

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!

Resolver I

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!

Resolver I

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?

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors