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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
haminguyen
Advocate I
Advocate I

Calculate percentages that will later be used to derive sales amounts in another table?

Hello, 

Here is the sample data: 

https://docs.google.com/spreadsheets/d/1_g6tayjUGmXcpbeXH55VHtnUQeNmjZmzHVma7NUau0Q/edit?usp=sharing...

 

For the same data, I have 2 questions (highlighted below in red) that I've been struggling with for a long time. I'd greatly appreciate your help. 

 

Background:

Question 1: I created the following calculated table (Calculated Table A) from dataset X, which summarizes sales amount by Fiscal Year, Country, Metric. How do I create the "% compared to Sales excl. VAT" column as highlighted in red?

"% compared to Sales excl. VAT" is defined as: For each Country and Fiscal Year, all Metrics are divided by "sales excl. VAT". For example, the 2nd row's expected value will be: 20/100 = 20%.

Calculated Table A (from dataset X, COUNTRY granularity)

Fiscal YearCountryMetricAmount% compared to Sales excl. VAT
LFYcountry1sales excl. VAT100100%
LFYcountry1cost2020%
LFYcountry1surplus8080%
Rolling 12 Monthscountry1sales excl. VAT200100%
Rolling 12 Monthscountry1cost6030%
Rolling 12 Monthscountry1surplus14070%
LFYcountry2sales excl. VAT150100%
LFYcountry2surplus9060%
Rolling 12 Monthscountry2sales excl. VAT180100%
Rolling 12 Monthscountry2surplus8044%

 

Question 2: I also created Calculated Table B (from a different dataset Y), which also summarizes sales amount by Fiscal Year, Country, Metric, but on City level. Another difference compared to the first table is that, Metric column in this table has only 1 value: "Sales excl. VAT".

Calculated Table B (from dataset Y, CITY granularity)

Fiscal YearCountryCityMetricAmount
LFYcountry1city1sales excl. VAT89
LFYcountry1city2sales excl. VAT10
Rolling 12 Monthscountry1city3sales excl. VAT179
LFYcountry2city4sales excl. VAT138
Rolling 12 Monthscountry2city4sales excl. VAT125

 

Now, using Calculated Table B as the base, how do I add Derived Amount column (below in red)?

Basically, to calculate Derived Amount, I need to:

  • first bring in all Metric values that appear in Calculated Table A (meaning not just "Sales excl. VAT", but also "Cost" and "Surplus"), as seen in the Metric column below. I guess this would require CROSSJOIN?;
  • then bring the corresponding % values in the 1st question into the "% compared to Sales excl. VAT" column, as seen below. The assumption is that Cities share the same percentages as their corresponding Countries;
  • then Derived Amount = [Amount of Sales excl. VAT] * [% compared to Sales excl. VAT]

Example expected output:

CountryFiscal YearcityMetricAmount% compared to sales excl. VATDerived Amount
country1LFYcity1sales excl. VAT89100%89
country1LFYcity1costnull20%18
country1LFYcity1surplusnull80%71
country2LFYcity4sales excl. VAT138100%138
country2LFYcity4surplusnull60%83
      

 

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @haminguyen ,

Please have a try.

First, Establishing the relationship between the two tables

11.PNG

Second, create a column.

Column = IF('Table A'[Metric]in {"cost","surplus"},'Table A'[Amount],BLANK())

Last, create measures.

compared = var sum_a=CALCULATE(SUM('Table A'[Column]),FILTER(ALL('Table A'),'Table A'[Country]=SELECTEDVALUE('Table A'[Country])&&'Table A'[Fiscal Year]=SELECTEDVALUE('Table A'[Fiscal Year])))
var big= IF(SELECTEDVALUE('Table A'[Metric])in {"sales excl. VAT"},SELECTEDVALUE('Table A'[Amount]),BLANK())
VAR BIG_COMPARE = CALCULATE(MAX('Table A'[Amount]),FILTER(ALL('Table A'),'Table A'[Country]=SELECTEDVALUE('Table A'[Country])&&'Table A'[Fiscal Year]=SELECTEDVALUE('Table A'[Fiscal Year])))
var result=IF(BIG_COMPARE>sum_a,BIG_COMPARE,sum_a)
var compare_=SELECTEDVALUE('Table A'[Amount])/result
return compare_

22.PNG

Derived Amount = IF([compared]=1,SELECTEDVALUE('Table  B'[Amount]),[compared]*SELECTEDVALUE('Table  B'[Amount]))

33.PNG

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @haminguyen ,

Please have a try.

First, Establishing the relationship between the two tables

11.PNG

Second, create a column.

Column = IF('Table A'[Metric]in {"cost","surplus"},'Table A'[Amount],BLANK())

Last, create measures.

compared = var sum_a=CALCULATE(SUM('Table A'[Column]),FILTER(ALL('Table A'),'Table A'[Country]=SELECTEDVALUE('Table A'[Country])&&'Table A'[Fiscal Year]=SELECTEDVALUE('Table A'[Fiscal Year])))
var big= IF(SELECTEDVALUE('Table A'[Metric])in {"sales excl. VAT"},SELECTEDVALUE('Table A'[Amount]),BLANK())
VAR BIG_COMPARE = CALCULATE(MAX('Table A'[Amount]),FILTER(ALL('Table A'),'Table A'[Country]=SELECTEDVALUE('Table A'[Country])&&'Table A'[Fiscal Year]=SELECTEDVALUE('Table A'[Fiscal Year])))
var result=IF(BIG_COMPARE>sum_a,BIG_COMPARE,sum_a)
var compare_=SELECTEDVALUE('Table A'[Amount])/result
return compare_

22.PNG

Derived Amount = IF([compared]=1,SELECTEDVALUE('Table  B'[Amount]),[compared]*SELECTEDVALUE('Table  B'[Amount]))

33.PNG

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

haminguyen
Advocate I
Advocate I

Hello, does anyone have any idea?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.