Dax Formula Help - Similar to Left Join Sum

[amotto11]

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

[v-lili6-msft]

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.

https://www.dropbox.com/s/yps97wcc5ntm2kg/Dax%20Formula%20Help%20-%20Similar%20to%20Left%20Join%20Su...

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

[amotto11]

@v-lili6-msft

you are oh so close to having it. the only thing that is missing is the AQ 2017Q4 at MQ 12 should also be a 3. I think using my table AQ and MQ should help. That has all possible combinations of the two, so possibly using that should help?

thank you again for your help!

[v-lili6-msft]

hi, @amotto11

     Why AQ2017Q4 and MQ12 is 3, MQ9 is the last for this row, it has no data after MQ12, and AQ2018Q1 has the same data

with AQ2017Q4, Why AQ2018Q1 is 1 2 3, but AQ2017Q4 is 1 2 3 3?

Best Regards,

Lin

[amotto11]

@v-lili6-msft

The simple answer is because it will complete the triangle. The long answer is pretty complicated and probably out of the scope of this thread. Basically you can think of AQ as a quarter and anything that happens in that quarter is captured along that row. The MQ is the maturity. Everything that happens during the quarter is contained in the 3 bucket, everything that happens in the next quarter that is associated with the last quarter is captured in the 6 bucket and so on and so forth. If nothing occured in the 12 bucket, it would be blank in my data because my data is incremental, i am looking to make it cumulative, since the quarter is 12 months old, the cumulative amount would still be the same as the 9 month old period.

The more you ask questions the more lights are going off in my head. Could you create a calculated column in the combined table that sums the data[amount] that is equal to the AQ but less than or equal to the MQ. Since all possible scenarios are contained in the combined table, would that work? Would you be able to help me with this?

Thanks again for all of your help!

[MFelix]

Hi @amotto11

For you to upload the file his must be done trough a onedrive, goggle drive, wetransfer and similar link.

Can you please share the file with this options and I can check your issue.

Regards,
MFelix

[amotto11]

@MFelix I have them uploaded to my google drive, but i am not sure how to share them with you. i apologize for my ignorance here.

[amotto11]

@MFelix 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!!