Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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)
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)
Ah ok, that was only my simple sample model and I actually implemented your DAX solution into a much more complicated model. I'll check out the data on that model.
Thanks again!
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}))
Proud to give back to the community!
Thank You!
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |