Forum Discussion
Resolver IIFiscal YTD when using MAX
Hi,
Revise the measure to:
Total BudgetAMT_Distinct = SUMX(GENERATE(DISTINCT(SampleData[Master_Sub_Account]),DISTINCT(dimDates[Month])),[Total BudgetAMT_Max])Hope this helps.
Super UserHi,
Not clear about your question. Is 4600000 the revenue budget of the entire year 2023 of New_value_15?
Resolver IIHi Ashish_Mathur ,
Sample figures are only for the Month of March 2023 to simplify matters. The Revenue budget should provide a result that equals 269,842,477.30, for the Month of March 2023
I do achieve this result using:
BudgetAMT_Max = MAX(factGLTransactions[Budget_Amount]) BudgetAMTDistinctTotal = SUMX(DISTINCT(factGLTransactions[Account]),[BudgetAMT_Max])
However not sure if Im just lucky and might run into problems.
Thus lets assume data is entered for Budget and the result is
269,842,477.30 for March
269,842,477.30 for April
500,000,000.00 for June
The Revenue YTD budget figure should be the sum of the Revenue total monhtly budget figure iow a Revenue Budget YTD figure of 1,039,684,954.60
However, the data provided with a column MasterSub Account that contains New_values from New_value_ to New_value_15. You will see that every New_value in the MasterSub Account contains a budget figure but duplicated for every txdate. It is also a different granulatiry if you look at the Revenue Amount numbers (hence the NULL values)
Hope the above clarifies?
Kind Regards
- Ashish_Mathur
I am still not clear. For me to be able to help you, share sample data (which can be pasted in an MS Excel file) and show the expected result in a Table format very clearly.
- Axiomite
Ashish_Mathur Let me know if the sample pbix file gives a better explanation. Link below.
https://drive.google.com/file/d/14IDcN7WeWHxzxA15Mmt5KaBLUUFAOEBU/view?usp=sharing
Thank you for your time.- Ashish_Mathur
Hi,
Revise the measure to:
Total BudgetAMT_Distinct = SUMX(GENERATE(DISTINCT(SampleData[Master_Sub_Account]),DISTINCT(dimDates[Month])),[Total BudgetAMT_Max])Hope this helps.