Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi Team,
Source Data
Date | category | Value | Type |
10/3/2021 | Commodity | 50 | Broker |
10/3/2021 | equity | 10 | Broker |
10/2/2021 | equity | 30 | Broker |
10/2/2021 | Commodity | 30 | Broker |
10/1/2021 | Commodity | 20 | Online |
10/1/2021 | equity | 30 | Online |
9/30/2021 | Commodity | 34 | Broker |
9/30/2021 | equity | 56 | Broker |
9/30/2021 | Commodity | 45 | Online |
9/30/2021 | equity | 50 | Online |
9/26/2021 | equity | 20 | Broker |
9/26/2021 | Commodity | 30 | Broker |
9/22/2021 | equity | 12 | Online |
9/22/2021 | Commodity | 14 | Online |
I have date Slicer
once i select any date it should display table like below
category | Broker | Online |
Commodity | 50 | 20 |
equity | 10 | 30 |
Requirement:- eg. if user select 3rd oct 2021 from the date slicer then it should display broker data of 3rd oct but online data with any lastest date i.e. 1st oct of value
if user select 26th sep 2021 then it should display data of broker data but online data of lastest date means 22nd sept value
if user select 1st oct 2021 then it should display data of broker data of 1st oct but online data of lastest date means 30th sept value not selected date value for online
Solved! Go to Solution.
Hi, @Anshenterprices
I'm a bit busy today so I didn't have time to reply. You need to use summraize function to calculate total.
You can try:
Online =
SUMX (
SUMMARIZE (
'Table 1',
[category],
[SubCategory],
[Date],
[Type],
[Value],
"online1",
VAR a =
MAXX (
FILTER (
ALL ( 'Table 1' ),
[Date] < SELECTEDVALUE ( 'Table 1'[Date] )
&& [Type] = "Online"
),
[Date]
)
RETURN
SUMX (
FILTER (
ALL ( 'Table 1' ),
[Date] = a
&& [Type] = "Online"
&& [category] = SELECTEDVALUE ( 'Table 1'[category] )
&& [SubCategory] = SELECTEDVALUE ( 'Table 1'[SubCategory] )
),
[Value]
)
),
[online1]
)
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Hi, @Anshenterprices
According to your description, I think you can create two measures and use it in table visual to display your desired result.
Like this:
Broker =
SUMX (
FILTER (
'Table',
[Type] = "Broker"
&& [Date] = SELECTEDVALUE ( 'Table'[Date] )
),
[Value]
)
Online =
VAR a =
MAXX (
FILTER (
ALL ( 'Table' ),
[Date] < SELECTEDVALUE ( 'Table'[Date] )
&& [Type] = "Online"
),
[Date]
)
RETURN
SUMX (
FILTER (
ALL ( 'Table' ),
[Date] = a
&& [Type] = "Online"
&& [category] = SELECTEDVALUE ( 'Table'[category] )
),
[Value]
)
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
@v-janeyg-msft Thank you so much .It's working. but once i use rowsubtotal in matrix table that time "online" row total is not appear. becase in the category there are sub-Category also in the data
Table looks like in below format
Category|SubCategory|Broker|Onlin|broker+Online|Borker-Online|
@Anshenterprices It is not recommended to use a matrix visual. Its context is more complicated. The measure is inherently dynamic and easy to change.
If you need to use it , Please provide sample data and your desired result, I will modify the measure.
Best Regards,
Community Support Team _ Janey
sample data
Date | category | SubCategory | Value | Type |
10/3/2021 | Commodity | com1 | 10 | Broker |
10/3/2021 | Commodity | Com2 | 40 | Broker |
10/3/2021 | equity | Eq1 | 10 | Broker |
10/3/2021 | equity | eq2 | 4 | Broker |
10/2/2021 | Commodity | com1 | 10 | Broker |
10/2/2021 | Commodity | com2 | 20 | Broker |
10/2/2021 | equity | Eq1 | 10 | Broker |
10/2/2021 | equity | Eq2 | 20 | Broker |
10/1/2021 | Commodity | com1 | 5 | Online |
10/1/2021 | Commodity | com2 | 15 | Online |
10/1/2021 | equity | Eq1 | 10 | Online |
10/1/2021 | equity | Eq2 | 20 | Online |
9/30/2021 | Commodity | com1 | 20 | Broker |
9/30/2021 | Commodity | com1 | 10 | Online |
9/30/2021 | Commodity | com2 | 34 | Broker |
9/30/2021 | Commodity | com2 | 45 | Online |
9/30/2021 | equity | Eq1 | 56 | Broker |
9/30/2021 | equity | Eq2 | 50 | Online |
9/30/2021 | equity | Eq1 | 56 | Broker |
9/30/2021 | equity | Eq2 | 50 | Online |
9/26/2021 | Commodity | com1 | 30 | Broker |
9/26/2021 | Commodity | com2 | 30 | Broker |
9/26/2021 | equity | Eq1 | 20 | Broker |
9/26/2021 | equity | Eq2 | 20 | Broker |
9/22/2021 | Commodity | com1 | 14 | Online |
9/22/2021 | Commodity | com2 | 5 | Online |
9/22/2021 | equity | Eq1 | 12 | Online |
9/22/2021 | equity | Eq2 | 12 | Online |
@v-janeyg-msft
Sample data:-
Category | Sub Category | Value | Date | CDate | Type |
Commodity | T1 | 19.43 | 9/29/2021 | 29-Sep-21 | Broker |
FNO | MF1 | 0.5 | 9/29/2021 | 29-Sep-21 | Broker |
FNO | B1 | 3.95 | 9/29/2021 | 29-Sep-21 | Broker |
FNO | c1 | 0.47 | 9/29/2021 | 29-Sep-21 | Broker |
Equity | ash1 | 7 | 9/29/2021 | 29-Sep-21 | Broker |
Equity | ab1 | 4 | 9/29/2021 | 29-Sep-21 | Broker |
Equity | te1 | 31.41 | 9/29/2021 | 29-Sep-21 | Broker |
FNO | s1 | 0.75 | 9/29/2021 | 29-Sep-21 | Broker |
Commodity | T1 | 19.45 | 9/30/2021 | 30-Sep-21 | Broker |
FNO | MF1 | 0.5 | 9/30/2021 | 30-Sep-21 | Broker |
FNO | B1 | 3.94 | 9/30/2021 | 30-Sep-21 | Broker |
FNO | c1 | 1.22 | 9/30/2021 | 30-Sep-21 | Broker |
FNO | U1 | -0.75 | 9/30/2021 | 30-Sep-21 | Broker |
Equity | ash1 | 7.78 | 9/30/2021 | 30-Sep-21 | Broker |
Equity | ab1 | 3.99 | 9/30/2021 | 30-Sep-21 | Broker |
Equity | te1 | 31.41 | 9/30/2021 | 30-Sep-21 | Broker |
FNO | s1 | 0 | 9/30/2021 | 30-Sep-21 | Broker |
Commodity | T1 | 19.65 | 10/1/2021 | 1-Oct-21 | Broker |
FNO | MF1 | 0.51 | 10/1/2021 | 1-Oct-21 | Broker |
FNO | B1 | 3.98 | 10/1/2021 | 1-Oct-21 | Broker |
FNO | c1 | 1.2 | 10/1/2021 | 1-Oct-21 | Broker |
FNO | U1 | -0.75 | 10/1/2021 | 1-Oct-21 | Broker |
Equity | ash1 | 7.82 | 10/1/2021 | 1-Oct-21 | Broker |
Equity | ab1 | 4 | 10/1/2021 | 1-Oct-21 | Broker |
Equity | te1 | 31.36 | 10/1/2021 | 1-Oct-21 | Broker |
Commodity | T1 | 19.69 | 10/4/2021 | 4-Oct-21 | Broker |
FNO | MF1 | 0.51 | 10/4/2021 | 4-Oct-21 | Broker |
FNO | B1 | 3.96 | 10/4/2021 | 4-Oct-21 | Broker |
FNO | c1 | 0.54 | 10/4/2021 | 4-Oct-21 | Broker |
FNO | U1 | -3.01 | 10/4/2021 | 4-Oct-21 | Broker |
Equity | ash1 | 7.77 | 10/4/2021 | 4-Oct-21 | Broker |
Equity | ab1 | 1.99 | 10/4/2021 | 4-Oct-21 | Broker |
Equity | te1 | 34.39 | 10/4/2021 | 4-Oct-21 | Broker |
FNO | s1 | 3.04 | 10/4/2021 | 4-Oct-21 | Broker |
Commodity | T1 | 19.45 | 10/5/2021 | 5-Oct-21 | Broker |
FNO | MF1 | 0.51 | 10/5/2021 | 5-Oct-21 | Broker |
FNO | B1 | 3.98 | 10/5/2021 | 5-Oct-21 | Broker |
FNO | c1 | 0.72 | 10/5/2021 | 5-Oct-21 | Broker |
FNO | U1 | -3.01 | 10/5/2021 | 5-Oct-21 | Broker |
Equity | ash1 | 7.71 | 10/5/2021 | 5-Oct-21 | Broker |
Equity | ab1 | 1.99 | 10/5/2021 | 5-Oct-21 | Broker |
Equity | te1 | 34.49 | 10/5/2021 | 5-Oct-21 | Broker |
FNO | s1 | 3.02 | 10/5/2021 | 5-Oct-21 | Broker |
Equity | ab1 | 0.040821 | 9/29/2021 | 29-Sep-21 | Online |
Equity | ab1 | 0.020821 | 10/1/2021 | 1-Oct-21 | Online |
Equity | ash1 | 0.075229 | 9/29/2021 | 29-Sep-21 | Online |
Equity | ash1 | 0.075229 | 10/1/2021 | 1-Oct-21 | Online |
Equity | te1 | 0.331673 | 9/29/2021 | 29-Sep-21 | Online |
Equity | te1 | 0.361673 | 10/1/2021 | 1-Oct-21 | Online |
Commodity | T1 | 0.199203 | 9/29/2021 | 29-Sep-21 | Online |
Commodity | T1 | 0.199203 | 10/1/2021 | 1-Oct-21 | Online |
FNO | B1 | 0.028357 | 9/29/2021 | 29-Sep-21 | Online |
FNO | B1 | 0.028357 | 10/1/2021 | 1-Oct-21 | Online |
FNO | MF1 | 0.00498 | 9/29/2021 | 29-Sep-21 | Online |
FNO | MF1 | 0.00498 | 10/1/2021 | 1-Oct-21 | Online |
2 measures:-
Broker_New1 =
SUMX (
FILTER (
'Table (3)',
[Type] = "Broker"
&& 'Table (3)'[Date]= SELECTEDVALUE ( 'Table (3)'[Date] )
),
'Table (3)'[Value]
)
Online_new1 =
VAR a =
MAXX (
FILTER (
ALL ( 'Table (3)' ),
[Date] < SELECTEDVALUE ( 'Table (3)'[Date] )
&& [Type] = "Online"
),
[Date]
)
RETURN
SUMX (
FILTER (
ALL ( 'Table (3)' ),
[Date] = a
&& [Type] = "Online"
&& [category] = SELECTEDVALUE ( 'Table (3)'[category] )
&& [Sub Category] = SELECTEDVALUE ( 'Table (3)'[Sub Category] )
),
'Table (3)'[Value]
)
In some cases Sub Total showing Blank, i need a row subtotal of each category
Hi, @Anshenterprices
I'm a bit busy today so I didn't have time to reply. You need to use summraize function to calculate total.
You can try:
Online =
SUMX (
SUMMARIZE (
'Table 1',
[category],
[SubCategory],
[Date],
[Type],
[Value],
"online1",
VAR a =
MAXX (
FILTER (
ALL ( 'Table 1' ),
[Date] < SELECTEDVALUE ( 'Table 1'[Date] )
&& [Type] = "Online"
),
[Date]
)
RETURN
SUMX (
FILTER (
ALL ( 'Table 1' ),
[Date] = a
&& [Type] = "Online"
&& [category] = SELECTEDVALUE ( 'Table 1'[category] )
&& [SubCategory] = SELECTEDVALUE ( 'Table 1'[SubCategory] )
),
[Value]
)
),
[online1]
)
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
User | Count |
---|---|
80 | |
77 | |
64 | |
48 | |
45 |
User | Count |
---|---|
103 | |
45 | |
39 | |
39 | |
36 |