Forum Discussion
Measure for Quarter Total to Only Show on Certain Rows
I have a table in Excel I am trying to recreate:
This is what I have in PBI so far:
I have created the following measure to give me the quarterly total of vendor spend and to only show on certain rows but as you can see in the visual it is only giving me the total for the month of the row it is on. For example, I want the Quarterly in February to show $11,458,412.94.
Quarterly Vendor Spend =
VAR _QTR1 = CALCULATE(SUM(Vendor[USD]), Vendor[MONTH] IN {1,2,3})
VAR _QTR2 = CALCULATE(SUM(Vendor[USD]), Vendor[MONTH] IN {4,5,6})
VAR _QTR3 = CALCULATE(SUM(Vendor[USD]), Vendor[MONTH] IN {7,8,9})
VAR _QTR4 = CALCULATE(SUM(Vendor[USD]), Vendor[MONTH] IN {10,11,12})
VAR _month = SELECTEDVALUE(DateTable[MonthName])
RETURN
IF(_month = "January", "",
IF(_month = "February", _QTR1,
IF(_month = "March", "",
IF(_month = "April", "",
IF(_month = "May",_QTR2,
IF(_month = "June", "",
IF(_month = "July", "",
IF(_month = "August",_QTR3,
IF(_month = "September", "",
IF(_month = "October","",
IF(_month = "November",_QTR4,
IF(_month = "December", "",
"")))
)))))))))
I am looking to do the same thing for the percent, but have it be percent of the current grand total of all months.
Is this possible with DAX? I have provided my .pbix file here: pbix file
Thanks!
Hi KMcCarthy9 ,
you can do it like this:
Take a look at the attached PBIX file.
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
6 Replies
Hi KMcCarthy9 ,
you can do it like this:
Take a look at the attached PBIX file.
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)This works, thank you!
However, is there some secret to how you got the Quarterly spend and % to show up for August and 0.00 for the other months that don't have a total yet? Mine just come out blank.Hi KMcCarthy9 ,
I used your sample data, which contains a value of 0 in column Total spend for August to December, so a zero is returned even when summing. If you want to be shown the value zero instead of blank, add a 0 in the appropriate DAX formula. Keep in mind: BLANK() + 0 = 0.
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
My IT Security doesn't allow us to download PBIX files, so this may be a shot in the dark, but try something like this? It may seem redudant, but when your VAR's are displayed on your visual, they are filtered by the Month they are assigend to. This will pull back in all the sales for the table, then your selection of Quarter will filter correctly.
Forrest
VAR _QTR1 = CALCULATE(SUM(Vendor[USD]), FILTER( ALL ( Vendor ), Vendor[MONTH] IN {1,2,3})) VAR _QTR2 = CALCULATE(SUM(Vendor[USD]), FILTER( ALL ( Vendor ), Vendor[MONTH] IN {4,5,6})) VAR _QTR3 = CALCULATE(SUM(Vendor[USD]), FILTER( ALL ( Vendor ), Vendor[MONTH] IN {7,8,9})) VAR _QTR4 = CALCULATE(SUM(Vendor[USD]), FILTER( ALL ( Vendor ), Vendor[MONTH] IN {10,11,12}))fhill Thanks for the suggestion, although it acted in the same way. I was hoping DAX could overrule the normal measure activity, but I guess not possible.
