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.
HI,
I have 1 table with a flag column act n bud in that table I have 11 value columns which I'm showing like this via matrix visual.
bud | act | |
sales | 10 | 12 |
cost | 15 | 7 |
nep | 4 | 8 |
gwp | 200 | 125 |
direct | 250 | 70 |
indirect% | 12% | 15% |
delta | 2 | 2 |
But I want to show it like this ,
bud | act | delta | |
sales | 10 | 12 | 2 |
cost | 15 | 7 | 8 |
nep | 4 | 8 | 4 |
gwp | 200 | 125 | 75 |
direct | 250 | 70 | 180 |
indirect% | 12% | 15% | -3.00% |
so instead of showing delta column below of indirect, I want to show it beside act n bud , delta of every row.
But the rows sales,cost, nep etc are not dax they are base value in Table
Hi @Anonymous ,
Has your problem been solved? If my post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Community Support Team _ kalyj
This is the data I have
Month | chanel | type | Channel | sales | cost | nep | gwp | direct% | indirect% | flag |
APR | msc | renewed | msc | 10.00 | 14,314.00 | 65,653.00 | 123.00 | 1,323.0% | 1,234.0% | act |
APR | msc | FRESH BUSINESS | msc | 12.00 | 1,142.00 | 3,141.00 | 23,453.00 | 6,500.0% | 1,423.0% | bud |
APR | req | used | msc | 0.00 | 124.00 | 789.00 | 654.00 | 23,400.0% | 1,543.0% | act |
APR | req | used | msc | 21.00 | 14,321.00 | 1,323.00 | 57.00 | 100.0% | 154.0% | bud |
APR | qwe | renewed | msc | 21.00 | 312.00 | 1,231.00 | 68,765.00 | 1,500.0% | 9,876.0% | bud |
APR | qwe | FRESH BUSINESS | msc | 6,778.00 | 1,323.00 | 987.00 | 3,456.00 | 87,65,400.0% | 5,78,690.0% | act |
Please insert this data in power query
then take matrix visual insert flag in column & gwp,nep etc in values now I need a delta column beside bud & act
bud | act | |
sales | 10 | 12 |
cost | 15 | 7 |
nep | 4 | 8 |
gwp | 200 | 125 |
direct | 250 | 70 |
indirect% | 12% | 15% |
delta | 2 | 2 |
Hi @Anonymous ,
I wonder how the value in your snapshot got, for example the sales of bud is 10 in the second visual, but in the first visual it's values are 12,21,21, I'm not very clear about the logic of your result.
If you want the sum sales of bud, it should be 54, and other value should be like this:
Best Regards,
Community Support Team _ kalyj
Hi @Anonymous ,
According to your description, in my understanding, delta is a result of the difference of bud and act, it’s hard to achieve in matrix by my test, but I get the same result by another method.
1.This is my sample by your description, I use pivot column in PowerQuery.
2.Cteate a measure delta.
delta =
CALCULATE (
MAX ( 'Table'[bud] ) - MAX ( 'Table'[act] ),
'Table'[Column2] = MAX ( 'Table'[Column2] )
)
3.Get the result.
4.IF you want all the value are negative, you can modify it like this:
delta =
VAR _V =
CALCULATE (
MAX ( 'Table'[bud] ) - MAX ( 'Table'[act] ),
'Table'[Project] = MAX ( 'Table'[Project] )
)
RETURN
IF ( _V < 0, _V * -1, _V )
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I want to share the pbix file here can someone tell how to do that?
Can you please share some data samples for this... If I assume that your table 1 is the original source then why aren't you creating a calculated column for delta by simply Actual-Budget.
Proud to be a Super User!
This is the data I have
Month | chanel | type | Channel | sales | cost | nep | gwp | direct% | indirect% | flag |
APR | msc | renewed | msc | 10.00 | 14,314.00 | 65,653.00 | 123.00 | 1,323.0% | 1,234.0% | act |
APR | msc | FRESH BUSINESS | msc | 12.00 | 1,142.00 | 3,141.00 | 23,453.00 | 6,500.0% | 1,423.0% | bud |
APR | req | used | msc | 0.00 | 124.00 | 789.00 | 654.00 | 23,400.0% | 1,543.0% | act |
APR | req | used | msc | 21.00 | 14,321.00 | 1,323.00 | 57.00 | 100.0% | 154.0% | bud |
APR | qwe | renewed | msc | 21.00 | 312.00 | 1,231.00 | 68,765.00 | 1,500.0% | 9,876.0% | bud |
APR | qwe | FRESH BUSINESS | msc | 6,778.00 | 1,323.00 | 987.00 | 3,456.00 | 87,65,400.0% | 5,78,690.0% | act |
I'm using flag in column matrix visual so it shows like this & insert all values gwp,sales etc in values
bud | act | |
sales | 10 | 12 |
cost | 15 | 7 |
nep | 4 | 8 |
gwp | 200 | 125 |
direct | 250 | 70 |
indirect% | 12% | 15% |
delta | 2 | 2 |
now I want to get delta for every row sales, gwp etc
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.