Forum Discussion
Dax Formula Help - Similar to Left Join Sum
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
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.
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
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!
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