Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
kilala
Resolver I
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 & ProductTurnover AmountTurnover % Total Turnover % Region
Region : A110(110/260) = 42.30%100%
       Product: 150(50/260) = 19.23%(50/110) = 45.45%
       Product : 2 30(30/260) = 11.54%(30/110) = 27.27%
       Product: 330(30/260) = 11.54%(30/110) = 27.27%
Region: B15057.70%100%
       Product:17026.93%(70/150) = 46.66%
       Product: 26023.07%(60/150) = 40%
       Product: 3207.69%(20/150) = 13.33%
TOTAL260100% 

 

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

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:

kilala_2-1652715295035.png

 

 

However, if I use ALLSELECTED(FactTable), and I filter certain Region slicer, the value of % Total here is changed, like example below:

kilala_3-1652715319847.png

 

 

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!

 

View solution in original post

9 REPLIES 9
DataInsights
Super User
Super User

@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

 

DataInsights_0-1652454206320.png

 





Did I answer your question? Mark my post as a solution!

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! 

@kilala,

 

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





Did I answer your question? Mark my post as a solution!

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!

 

kilala_0-1652708276193.png

 

@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.





Did I answer your question? Mark my post as a solution!

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:

kilala_0-1652712201292.png

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.





Did I answer your question? Mark my post as a solution!

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:

kilala_2-1652715295035.png

 

 

However, if I use ALLSELECTED(FactTable), and I filter certain Region slicer, the value of % Total here is changed, like example below:

kilala_3-1652715319847.png

 

 

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!

 

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!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.