Forum Discussion
Helper IIDax Formula Help - Similar to Left Join Sum
Hello All,
I am trying to obtain a DAX formula to sum incremental data into cumulative for a data table. The only problem is that i don't have all possible scenarios in my data table, so there is information that is left out, essentially it should be 0. Since i cannot upload my data or power bi report that i have built so far, i will try to do screen shots. Note that this data is just sample data. Please let me know if there is someway i can upload my excel and power bi workbooks for you to play with.
I have 4 initial tables with some joins
DataAQ and MQ PossibiilityAQMQJoins
when i show all possible values in my data table, i have my formula working correctly with the correct output. As you can see the data triangle is complete. with all values showing the cumulative amount.
Combined with all cumulative data
When you remove the 12 MQ from the data set, since it is 0, it is being removed from my triangle. right now my formula is a calculated column, but i would really like to do this as a measure. Below you can see the output when 12 MQ is removed from my table. I would like it to look the same as above, since i am using the MQ table as my column
Data No 12 MQIncorrect Result (no 12 MQ)
Any help is greatly appretiated
9 Replies
amotto11MFelix I think i have it.
This is the Power BI desktop
https://drive.google.com/open?id=1CXI6Dr00oONhFXPbgwi49mP3amEWkG2M
This is the raw data
https://drive.google.com/open?id=1X-uWJG7McORjfanmvBPPzlYgSDSC0uPg
Thanks for any help you can provide!!
v-lili6-msftCommunity Support
hi, amotto11
After my research, you can do these as below:
right-click field AQ in Rows Values then select Show items with no data
Result:
And you would like to do column as a measure
in your basic data, the total amount is just 24 but in your visual total is 62, is it correct?
I have written two formulas
Measure = CALCULATE(SUM(Data[Amount]),FILTER(Data,Data[AQ]=MAX(AQ[AQ])&&Data[MQ]<=MAX(MQ[MQ])))
Measure 2 = var _table=GENERATE(VALUES(AQ[AQ]),VALUES(MQ[MQ])) return SUMX(_table,[Measure])
Result:
here is pbix, please try it.
Best Regards,
Lin
- amotto11
Hello v-lili6-msft,
Thank you for your help, but it doesn't appear this is what i am looking for. I need the 12 column to show, but i also need the numbers in it as if there were 0's. My dataset that i provided might not have been the easiest to play with since everything is 1's, but essentially what i am trying to do is sum accross the columns (MQ) for each AQ and if the column doesn't exist in the data it needs to function as if there are 0's in it. Essentially i am looking for a triangle where the rows are summed up cumulatively instead of incremental 1's. In the end i am looking to graph this data along the row for each AQ so i can't have a hole where there is no number, it has to just be the number before it. AQ 2017Q1 should read 1 2 3 3 4 5 6, there should not be a blank. AQ 2017Q2 should be 1 2 3 3 4 5, 2017Q3 should be 1 2 3 3 4, 2017Q4 should be 1 2 3 3, 2018Q1 1 2 3, 2018Q2 1 2 and 2018Q3 should just be 1. I think your Measure 1 has the right motive, but it isn't working because all of the data is still 1. I am not cuncerned about the subtotals or totals for the columns or rows, just the triangle of cumulative data which will be graphed as a line graph in the end.
For some background, this data is coming from SQL, but sometimes an AQ or a MQ is not included in the data, so it is essentially 0. Instead of writing a query to create the 0 row in the database, because there could be 10 to the 10th scenarios of 0's which would increase the table size exponentially, i am looking to handle it in Power BI. Essentially the AQ and MQ tables contain all possible MQ's and AQ's in the data, so that is why i am using them for the row and column rather than the AQ and MQ in the data fields. My actual data has around 8 more variables that i will get all of the possible scenarios and put them in their own tables.
In the end i am looking for something like this where AQ is the legend and MQ is the axis:
I hope this helps you to help me! Thanks again for your assistance.
- v-lili6-msft
hi, amotto11
After my research, you may try to use this formula as below:
Measure = IF ( CALCULATE ( MAX ( Data[MQ] ), ALL ( MQ ) ) >= MAX ( MQ[MQ] ), CALCULATE ( SUM ( Data[Amount] ), FILTER ( ALL ( Data ), Data[AQ] = MAX ( AQ[AQ] ) && Data[MQ] <= MAX ( MQ[MQ] ) ) ), BLANK () )Result:
Best Regards,
Lin
