Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I have to re-create the sample view where % Expected Payments and % Expected Accounts are division calculations.
Slicer 1 : Month
Slicer 2: Division metrics - % Expected Payments and % Expected Accounts
Slicer 3 & 4 : Company and Unit
Graph : Line Chart with x axis as days, value as division calculations and legend as month
At present, I created another table like this:
Attribute | Value |
% Expected Payments | 1 |
% Expected Accounts | 2 |
In my data, I added two columns to hold this information and did unpivot.
I linked both the tables with the column Attribute and used the below DAX to do the calculations:
Measure = IF(CONTAINS(Table1,Table1[Attribute],"% Expected Payments"),DIVIDE(SUM(Table1[Payments]),SUM(Table1[Estimated Payments])),IF(CONTAINS(Table1,Table1[Attribute],"% Expected Accounts"),DIVIDE(SUM(Table1[Accounts]),SUM(Table1[Estimated Accounts])),0))
This solution works for me.
However,
I have millions of data and 30 attributes. This solution will increase my data rows to : Millions of Data * 30, which is a lot.
Every month, the data increases in size and I have to store that data in Power-BI Desktop.
Is there any way to achieve the same scenerio without doing the unpivot option in the actual data ? Maybe by creating different measure for each metric and proceeding somehow to get the correct calculation ?
Any help is appreciated.
Please find my sample solution of dummy data for your alteration here :
https://drive.google.com/open?id=1UydHHYd4iBVDTu7xoWfRoHM3OYC4lgQZ
Thanks & Regards,
Sunaina Khera
Solved! Go to Solution.
Hi @Anonymous ,
1. Remove all table transformations from Power Query steps.
2. Create two Measures %ExpectedAccounts and %ExpectedAccounts
3. Disconnected Slicer with a Measure: Create a measure that will use the concept of disconnected slicer with SWITCH() function.
https://powerpivotpro.com/2018/05/disconnected-slicers-with-dax-variables-selectedvalues/
%ExpectedAccounts =
DIVIDE (
SUM ( 'Table1 (2)'[Accounts] ),
SUM ( 'Table1 (2)'[Estimated Accounts] )
)
%ExpectedPayments =
DIVIDE (
SUM ( 'Table1 (2)'[Payments] ),
SUM ( 'Table1 (2)'[Estimated Payments] )
)
AmountMeasure =
VAR SlicerValue =
SELECTEDVALUE ( 'Attribute Mapper'[Value] )
RETURN
SWITCH ( SlicerValue, 2, [%ExpectedAccounts], 1, [%ExpectedPayments], BLANK () )
Thanks
Deepak Gupta
BI Consultant @Prodata
@Anonymous , refer this
Hi @amitchandak ,
Thanks for the revert. But, I am not sure if this post is helpful in my scenerio where I want to put together the divison measure in one slicer for the graph. If I use this approach, I can have the legends of the division metrics in the graph with the metrics as slicer but won't be able to use the month as legend.
Please correct me if I am wrong?
Hi @Anonymous ,
1. Remove all table transformations from Power Query steps.
2. Create two Measures %ExpectedAccounts and %ExpectedAccounts
3. Disconnected Slicer with a Measure: Create a measure that will use the concept of disconnected slicer with SWITCH() function.
https://powerpivotpro.com/2018/05/disconnected-slicers-with-dax-variables-selectedvalues/
%ExpectedAccounts =
DIVIDE (
SUM ( 'Table1 (2)'[Accounts] ),
SUM ( 'Table1 (2)'[Estimated Accounts] )
)
%ExpectedPayments =
DIVIDE (
SUM ( 'Table1 (2)'[Payments] ),
SUM ( 'Table1 (2)'[Estimated Payments] )
)
AmountMeasure =
VAR SlicerValue =
SELECTEDVALUE ( 'Attribute Mapper'[Value] )
RETURN
SWITCH ( SlicerValue, 2, [%ExpectedAccounts], 1, [%ExpectedPayments], BLANK () )
Thanks
Deepak Gupta
BI Consultant @Prodata
View example:
Please refer to the attached file solution https://drive.google.com/open?id=1cLPKeis3HQVft1GuexRtlEjrV8_MtFlT
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |