Forum Discussion
filter context
Hello, I am needing help with proper presentation of values. Please follow this link to download the file.https://1drv.ms/u/s!AmBVCme14p7xlTPEccsS9EpmSk1a?e=a7Q5CX
Below is a snippit of the data - the key thing to note Item ID includes the fuel type as well as fed/state taxes.
| Invoice | Invoice Date | ItemID | Description | Quantity | Amount |
| 4152 | 6/20/2020 | GAS | 6/18/2020: JW: T004 87 Ravens: T004 87 Ravens | 1 | 2.75 |
| 4152 | 6/20/2020 | GAS | 6/18/2020: JW: T004 87 Ravens: T004 87 Ravens | 1 | 2.75 |
| 4152 | 6/20/2020 | GAS | 6/19/2020: JW: T004 87 Ravens: T004 87 Ravens | 1 | 2.75 |
| 4161 | 6/27/2020 | DIESEL | 6/23/2020: JW: #50 05 Peterbilt: 50 Peterbilt | 36 | 115.2 |
| 4161 | 6/27/2020 | GAS | 6/23/2020: JW: T004 87 Ravens: T004 87 Ravens | 1 | 3 |
| 4161 | 6/27/2020 | GAS | 6/22/2020: JW: T004 87 Ravens: T004 87 Ravens | 1 | 3 |
| 4161 | 6/27/2020 | Federal Taxes | 6/23/2020: JW: #50 05 Peterbilt: 50 Peterbilt | 36 | 8.78 |
| 4161 | 6/27/2020 | State Taxes | 6/23/2020: JW: #50 05 Peterbilt: 50 Peterbilt | 36 | 10.66 |
Here;s the result of my initial attempt
Using this measure
FedTaxAmount = calculate([SumAmount],Data[ItemId]="Federal Taxes")
How can the above measure be modified to exclude the subtotal for Gas. The common datafield between diesel fuel type and taxes is the description field.
The second question is an additional tweak to avoid improper results depending on visualization. To prevent potential user confusion - would like to prevent these two items from showing up in rows. In the first example, I was able to use external filters to remove from matrix, but I want to hard code the formula to prevent an unexpecting user confusion. I will also eventually want to move this to excel, and utilize cubefunctions.
Again, here is link to the sample file.
https://1drv.ms/u/s!AmBVCme14p7xlTPEccsS9EpmSk1a?e=a7Q5CX
thanks - and always appreciate the forum's support!
Having the Item in the same column and on the visual makes this trickier. FYI that it would be simpler to just pivot out the Item ID column in a Matrix visual to get almost your desired output.
However, if you want a measure that does what you want, including not showing values for when Item = Federal or State Taxes, please try this expression:
FedTaxAmountNew = VAR __total = SUMX ( DISTINCT ( Data[Description] ), CALCULATE ( [SumAmount], Data[ItemId] = "Federal Taxes" ) ) VAR __thisitem = SELECTEDVALUE ( Data[ItemId] ) RETURN IF ( OR ( __thisitem = "Federal Taxes", __thisitem = "State Taxes" ), BLANK (), __total )You can just replace Federal with State for the other measure.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
4 Replies
Having the Item in the same column and on the visual makes this trickier. FYI that it would be simpler to just pivot out the Item ID column in a Matrix visual to get almost your desired output.
However, if you want a measure that does what you want, including not showing values for when Item = Federal or State Taxes, please try this expression:
FedTaxAmountNew = VAR __total = SUMX ( DISTINCT ( Data[Description] ), CALCULATE ( [SumAmount], Data[ItemId] = "Federal Taxes" ) ) VAR __thisitem = SELECTEDVALUE ( Data[ItemId] ) RETURN IF ( OR ( __thisitem = "Federal Taxes", __thisitem = "State Taxes" ), BLANK (), __total )You can just replace Federal with State for the other measure.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Great - thank you! I was able to make work. Plus, I even got moved over into excel and have cube functions pulling in correctly. What I'm trying to do is create an excel invoicing system - and with your help - think this could become reality.
As for your comment - pivot the items, I had tried that, but was unable to get the exact tax items to pivot, but leaving the others. Plus, I have other reports working off this one particular table, so I opted to try it the DAX way. But, you are right, getting the two tax items as columns, would have been much simpler. Thanks again - this forum is great!
I appreciate your help - the only way I can learn - is to learn from others. Thank you!
Try change your filter to include KEEPFILTERS.
FedTaxAmount = calculate([SumAmount],KEEPFILTERS(Data[ItemId]="Federal Taxes"))StateTaxAmount = calculate([SumAmount],KEEPFILTERS(Data[ItemId]="State Taxes"))You can read about KEEPFILTERS in this article.
Thanks - that got me closer, and would have worked except with the request to "exclude" the fed/state tax items in the row dimension. The other individuals solution seemed to work better in this spot.
I appreciate the link to the artical on keepfilters. I had read it before, but there is so much to learn - I wished I had a photographic memory to remember all this nuances. But great stuff and thank you for your assistance.
thank you!
