Ratio Calculation
I need to calculate a ratio. I have a table with one column for the amount. There is a column that holds the ClientID and another column that holds the Sub Account for those ClientID. A ClientID may have more than one SubAccount. I need to calculate the ratio held in each Sub Account for ClientIDs. I have my dimension table set up with unique Sub Accounts and corresponding ClientIDs. How should I arrange my table so I an achieve calculating the ratio? What should my DAX look like? This is what I have so far for my DAX... TotalAmountPerClient = CALCULATE(SUM('MainTable'[Amount]), ALLEXCEPT(AccoutNumber, AccountNumber[ClientID])) TotalAmountPerSubaccount = SUM('MainTable'[Amount]) Ratio = DIVIDE([TotalAmountPerClient] , [TotalAmountPerSubaccount]) Any help appreciated AdrianCalculate monthly distribution between two tables
Hi, I've been trying to create a report which estimates the monthly distribution based on two tables. Table 1 contains Year-Month, country and weight based on shipping data. Year-Month Country Weight Kg 2021-01 Norway 15 2021-01 Canada 2 2021-02 Norway 10 2021-02 Brazil 3 Table 2 contains Year-Month, weight, and attributes based on transactions. Year-Month Weight Kg Attribute 1 Attribute 2 Attribute 3 2021-01 12 A A B 2021-01 5 B C A 2021-02 3 C B A 2021-02 9 B B B I'm trying to estimate the distribution of each attribute with each country based weight distribution per month. My plan is to calculate the total attribute weight per month, get a ratio and then distribute it to each country. The total weight per month doesn't need to add upp to the toal attribute weight per month, only the ratios are important. In this example I want the following: 2021-01: Total attribute weight 17 Kg, 12/17 has [A,A,B] and 5/17 has [B,C,A] 2021-02: Total attribute weight 12 Kg, 3/12 has [C,B,A] and 9/12 has [B,B,B] The end table should then show Year-month Country Attribute Weight Kg Attribute 1 Attribute 2 Attribute 3 2021-01 Norway 12*(12/17) A A B 2021-01 Norway 12*(5/17) B C A 2021-01 Canada 5*(12/17) A A B 2021-01 Canada 5*(5/17) B C A 2021-02 Norway 10*(3/12) C B A 2021-02 Norway 10*(9/12) B B B 2021-02 Brazil 3*(3/12) C B A 2021-02 Brazil 3*(9/12) B B B Any help regarding this would be greatly apprechiated!Simple Cost Ratio from GL Data
Hello all, pretty frustrated with what I feel should be a simple task I have a simple set of GL data, as seen below, where my amount is in one column, and my accounts are rows. I then do a very simple model where I have a GL Account class lookup table, which assigns the class "revenue" and "cost" to each account. I then just want to have a simple cost ratio at the account level, of cost/total revenue. the table should look as such below, but nothing returns at the account level for a margin. even the revenue accounts are wrong, as Revenue Reg should be 60% of total revenue, , and Revenue Spec should be 40% My measures are Value = SUM(data_table[Amount]) Total Revenue = CALCULATE([Value],gl_class[Class]="Revenue") Cost Margin = DIVIDE([Value],[Total Revenue]) any help would be greatly appreciated
