Calculate Percentage by Region & Product

[kilala]

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!

[DataInsights]

@kilala,

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

[kilala]

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! 

[DataInsights]

@kilala,

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

[kilala]

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!

[DataInsights]

@kilala,

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.

[kilala]

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.

[DataInsights]

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.

[kilala · Accepted 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!

[kilala]

Hi @DataInsights 

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!