Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Good Day!
I think I have a rather complex situation and would greatly apprecaite some help.
Specifically, I am not able to correctly determine the row, column or grand totals at the 'Site' level/context. The only thing that does correctly show is the row total in the context of the item number.
I have based this attempt off Enterprise DNA's youtube video:
https://www.youtube.com/watch?v=eoT-jPzV3Wk&ab_channel=EnterpriseDNA
https://forum.enterprisedna.co/t/pbix-file-for-fixing-matrix-totals-youtube-video/17559
I think my virtual table 'vTable' is not correct. The invoice history table provides line level detail and has well over 1mn records. The expected output would be a matrix filterable by site and item with correct totals.
I have also tried looking at custom visuals such as Acterys Matrix Light but its does not allow for enough data to be displayed at once.
Lastly, I cannot provide a pbix file.
Customer Inflation with Fixed Totals (not working) =
VAR vTable =
ADDCOLUMNS(
CROSSJOIN(
VALUES('Date'[Month]),
VALUES('Invoice History'[Item Number])
),
"@inflation", [Customer Inflation]
)
VAR TotalInflation =
SWITCH(
TRUE(),
HASONEVALUE('Date'[Month]) && (HASONEVALUE('Invoice History'[Site]) || HASONEVALUE('Invoice History'[Item Number])),
[Customer Inflation], // Base data
HASONEVALUE('Date'[Month]),
CALCULATE(
SUMX( vTable,
[@inflation]
),
VALUES('Invoice History'[Item Number])
), // column totals,
HASONEVALUE('Invoice History'[Item Number]), // row totals
CALCULATE(
SUMX(
vTable,
[@inflation]
),
VALUES('Date'[Month])
), // grand total
SUMX(
vTable,
[@inflation]
)
)
RETURN
TotalInflation
Solved! Go to Solution.
Customer Inflation with Fixed Totals =
VAR vTable =
ADDCOLUMNS(
SUMMARIZE('Invoice History',
'Date'[Fiscal Year Number],
'Date'[Month],
'Invoice History'[Site],
'Invoice History'[Item Number]
),
"@inflation", [Customer Inflation]
)
VAR TotalInflation =
SWITCH(
TRUE(),
HASONEVALUE('Date'[Month]) && HASONEVALUE('Invoice History'[Item Number]),
[Customer Inflation], // Base data
HASONEVALUE('Date'[Month]),
CALCULATE(
SUMX( vTable,
[@inflation]
)
), // column totals,
HASONEVALUE('Invoice History'[Item Number]), // row totals
CALCULATE(
SUMX(
vTable,
[@inflation]
),
VALUES('Date'[Month])
), // grand total
SUMX(
vTable,
[@inflation]
)
)
RETURN
TotalInflation
Working solution
Customer Inflation with Fixed Totals =
VAR vTable =
ADDCOLUMNS(
SUMMARIZE('Invoice History',
'Date'[Fiscal Year Number],
'Date'[Month],
'Invoice History'[Site],
'Invoice History'[Item Number]
),
"@inflation", [Customer Inflation]
)
VAR TotalInflation =
SWITCH(
TRUE(),
HASONEVALUE('Date'[Month]) && HASONEVALUE('Invoice History'[Item Number]),
[Customer Inflation], // Base data
HASONEVALUE('Date'[Month]),
CALCULATE(
SUMX( vTable,
[@inflation]
)
), // column totals,
HASONEVALUE('Invoice History'[Item Number]), // row totals
CALCULATE(
SUMX(
vTable,
[@inflation]
),
VALUES('Date'[Month])
), // grand total
SUMX(
vTable,
[@inflation]
)
)
RETURN
TotalInflation
Working solution
Thank you so much for the reply! My invoice table does have a relationship to the date table, you are correct. I had previous tried variations of using summarize.
Your summarize provides the following:
The crossjoin is the same as well.
The reason I was using the variable table was because the measure [Customer Inflation] is using a running total. It does a comparison to the current month to the previous ones within the year. So for the totals, there is no context to compare for the measure.
Sales RT =
CALCULATE(
[Total Sales],
FILTER(
ALLSELECTED('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)
I don't understand why all these cases are necessary. What's wrong with the following?
SUMX (
CROSSJOIN (
VALUES ( 'Date'[Month] ),
VALUES ( 'Invoice History'[Item Number] )
),
[Customer Inflation]
)
If your invoice table has a relationship to the date table, then a more efficient version might work:
SUMX (
SUMMARIZE (
'Invoice History',
'Invoice History'[Item Number],
'Date'[Month]
),
[Customer Inflation]
)
User | Count |
---|---|
91 | |
88 | |
88 | |
79 | |
49 |
User | Count |
---|---|
153 | |
145 | |
106 | |
74 | |
55 |